Worksheets dilemma
 

Help! (please)

I have a workbook that is a Field Report. It starts with one visible sheet and when the user changes a certain cell to a particular piece of text a "new" sheet is "created" - in reality it is simply an existing sheet which is unhidden by the code. This "new" sheet is called "OEM". The workbook has lots of VBA code for Workbook_BeforeSave which checks that some mandatory cells have been completed on OEM and other sheets. All is working well. But . . . It may be that the user wants to create another OEM sheet. I've achieved this by copying the orinal OEM sheet, so we have OEM and OEM(1). All good so far, except of course the VBA code does not work for the new sheet "OEM(1)"

I hope that's explained clearly enough. So my question is, what is the best way to get around this i.e. get some VBA code to run on OEM(1)? I realise I could create more code for potential new sheets but am hoping there is a more elegant way?

Any help gratefully received.

Steve

Worksheets dilemma
 

Hi Steve,

Am Wed, 9 Dec 2015 06:58:52 -0800 (PST) schrieb :

I have a workbook that is a Field Report. It starts with one visible sheet and when the user changes a certain cell to a particular piece of text a "new" sheet is "created" - in reality it is simply an existing sheet which is unhidden by the code. This "new" sheet is called "OEM". The workbook has lots of VBA code for Workbook_BeforeSave which checks that some mandatory cells have been completed on OEM and other sheets. All is working well. But . . . It may be that the user wants to create another OEM sheet. I've achieved this by copying the orinal OEM sheet, so we have OEM and OEM(1). All good so far, except of course the VBA code does not work for the new sheet "OEM(1)"

I hope that's explained clearly enough. So my question is, what is the best way to get around this i.e. get some VBA code to run on OEM(1)? I realise I could create more code for potential new sheets but am hoping there is a more elegant way?

try in Workbook_BeforeSave

For Each wsh In Worksheets
If Left(wsh.Name, 3) = "OEM" Then
your code


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Worksheets dilemma
 

Hi Claus

I've had an issue with this where the macro works fine but some additional lines are only working if the sheet is the active sheet. For example, if I'm on OEM sheet and run the code below it works fine; if I'm on another sheet (Let's call it 'Site Details'), the first piece of code works but the second piece (which is evaluating if D55 equals zero) says there is no picture even though there is and D55 has a number 1 in it. I can't spot any reason that this would happen!

Thanks.

Steve

For Each Wsh In Worksheets
If Left(Wsh.Name, 3) = "OEM" Then
With Wsh
If Len(.Range("G16")) = 0 Then
'Cancel = True
MsgBox "You have left the field G16 in sheet " & Wsh.Name & vbNewLine & "'Amount Failed'' blank. Please fill it out." & vbNewLine & "You can exit from this procedure by clicking 'No' on the next dialog box."
End If
End With
End If
Next Wsh

For Each Wsh In Worksheets
If Left(Wsh.Name, 3) = "OEM" Then
With Wsh
If Range("D55") = 0 Then
'Cancel = True
MsgBox "There is no photo of the Luminaire Product label on " & Wsh.Name & vbNewLine & "You can exit from this procedure by clicking 'No' on the next dialog box."
End If
End With
End If
Next Wsh

Worksheets dilemma
 

Hi Steve,

Am Mon, 14 Dec 2015 03:42:12 -0800 (PST) schrieb :


For Each Wsh In Worksheets
If Left(Wsh.Name, 3) = "OEM" Then
With Wsh
If Len(.Range("G16")) = 0 Then
'Cancel = True
MsgBox "You have left the field G16 in sheet " & Wsh.Name & vbNewLine & "'Amount Failed'' blank. Please fill it out." & vbNewLine & "You can exit from this procedure by clicking 'No' on the next dialog box."
End If
End With
End If
Next Wsh

For Each Wsh In Worksheets
If Left(Wsh.Name, 3) = "OEM" Then
With Wsh
If Range("D55") = 0 Then
'Cancel = True
MsgBox "There is no photo of the Luminaire Product label on " & Wsh.Name & vbNewLine & "You can exit from this procedure by clicking 'No' on the next dialog box."
End If
End With
End If
Next Wsh

you can put this together in one loop.
Try:

For Each Wsh In Worksheets
If Left(Wsh.Name, 3) = "OEM" Then
With Wsh
If Len(.Range("G16")) = 0 Or Len(.Range("D16")) = 0 Then
'Cancel = True
MsgBox "You have left the field G16 in sheet " & Wsh.Name
& vbNewLine & _
"'Amount Failed'' blank. Please fill it out." &
vbNewLine & _
"You can exit from this procedure by clicking 'No'
on the next dialog box."
End If
End With
End If
Next Wsh

Or:

Dim myRng As Range, rngC As Range

For Each Wsh In Worksheets
If Left(Wsh.Name, 3) = "OEM" Then
With Wsh
Set myRng = .Range("D16,G16")
For Each rngC In myRng
If Len(rngC) = 0 Then
'Cancel = True
MsgBox "You have left the field " & rngC.Address & " in
sheet " & Wsh.Name & vbNewLine & _
"'Amount Failed'' blank. Please fill it out." &
vbNewLine & _
"You can exit from this procedure by clicking 'No'
on the next dialog box."
End If
Next
End With
End If
Next Wsh


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Worksheets dilemma
 

Superb! Thanks again Claus. I've adapted it a bit and it's doing exactly what I want. I was stumped for a long time until I realised the line Cancel = True was cancelling things and not allowing it to go on and do the save.

Cheers!

Steve