Code Help I am Using Below Code & I am Trying To Add More Cells To Bold Section
ID: 639767 • Letter: C
Question
Code Help
I am Using Below Code & I am Trying To Add More Cells To Bold Section How Can I Go About This Without Errors
Private Function FinalTest() As Boolean
'prevents saving or closing while there is invalid
'information on any of the weekly sheets
'called from:
'Workbook_BeforeClose()
'returns TRUE if error found
'and that's assigned to Cancel in the caller
Dim anyWS As Worksheet
'values to deal with weekly sheets
Const weeklySheetPhrase = "Weekly Production Summary (Direct Manpower) & OEE"
Const keyPhraseCell = "C2"
Const keyCellAddress = "AV46,BE46,BN46,AV90,BE90,BN90,AV134,BE134,BN134AV178,BE178,BN178,AV222,BE222,BN222,AV266,BE266,BN266"
FinalTest = False ' just to be sure
For Each anyWS In ThisWorkbook.Worksheets
'if they are OK
'for testing
'weekly sheets.
'we'll examine for problems
'with the weekly sheets. Assumption here is that
'each of those sheets, and ONLY those sheets has
'the weeklySheetPhrase in cell keyPhraseCell
'at this time that is "Weekly Production Summary (Direct Manpower) & OEE" in C2 on those sheets only.
If UCase(Trim(anyWS.Range(keyPhraseCell))) = UCase(Trim(weeklySheetPhrase)) Then
'this is a weekly sheet of interest to us
'only permit save/close if value in AF356 is
'is 0.85 , inclusive.
If anyWS.Range(keyCellAddress) <> 0 And _
(anyWS.Range(keyCellAddress) < anyWS.Range("S3")) Then
MsgBox "Warning" & whichProcess & " Worksheet - Invalid Value On Sheet (Enter Or Adjust Your DownTime Entries ) " _
& anyWS.Name
FinalTest = True ' Proceed close!
anyWS.Activate ' take them to the sheet with error
Exit Function
End If
End If
thanks !!
Explanation / Answer
Hi..
What error are you getting?
It is a long shot because this forum also wraps text, but if your line is wrapping in the VBA editor, you need to use aspace followed by an underscore symbol to show that the line is wrapping. If you need to wrap a long text string, then you need to exit the text string in order to use the space/underscore.
Example:
MyRangeCells = "AB, AC, AK" is fine, but to run on multiple lines you need something like
MyRangeCells = "AB, AC, " & _
"AK"
or
MyRangeCells = _
"AB, AC, AK"
note that you can indent the subsequent lines as desired, and use multiple lines if needed:
MyRangeCells = _
"AB, " & _
"AC, " & _
"AK"
The other possible issue is that if you are trying to assign text as cell addresses, then BN134AV178 is not a valid cell address.