convert macro to single sheet

[Smurfit-Stone]

Hello Board,

I have a macro that doesn't allow the user to print or save with entering a delivery date. the macro is place in "This Workbook" module. My problem is I want the macro to run according to one worksheet in particular, not the whole workbook, can anyone tell me where I can place or how I can redo this procedure. Macro below...Thanks in advance.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If Sheet1.Range("$L$4") = "" Then
Do While Not IsDate(Range("L$4"))
MsgBox "Delivery date required"
Sheet1.Range("$L$4") = InputBox("Please Enter Delivery Date")
Loop
End If
Me.Save
End Sub

 

[VoG]

Try this - change Sheet1 if necessary

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim ws As Worksheet, Flag As Boolean
For Each ws In ActiveWindow.SelectedSheets
    If ws.Name = "Sheet1" Then
        Flag = True
        Exit For
    End If
Next ws
If Not Flag Then Exit Sub
If Sheet1.Range("$L$4") = "" Then
    Do While Not IsDate(Range("L$4"))
        MsgBox "Delivery date required"
        Sheet1.Range("$L$4") = InputBox("Please Enter Delivery Date")
    Loop
End If
Me.Save
End Sub

 

[Smurfit-Stone]

Hi Peter,

This didn't work for me. It still allowed the page to be printed and saved. I actually have two worksheet in this workbook, one named "Quote" and the other "Order". I want the Order sheet to always have a delivery date before printing or saving. The Quote sheet I want to be able to print without a delivery date. The two sheet are identical except for a formula that calculates a discount, so cell L4 on both sheet is the place for delivery date. I'm not concerned with a delivery date on Quote, but I need one on Order before the user can print or save. Is it possible to only have code pertain to one sheet?

 

[VoG]

Did your previous code work?

Try this

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim ws As Worksheet, Flag As Boolean
For Each ws In ActiveWindow.SelectedSheets
    If ws.CodeName = "Sheet1" Then
        Flag = True
        Exit For
    End If
Next ws
If Not Flag Then Exit Sub
If Sheet1.Range("$L$4") = "" Then
    Do While Not IsDate(Range("L$4"))
        MsgBox "Delivery date required"
        Sheet1.Range("$L$4") = InputBox("Please Enter Delivery Date")
    Loop
End If
Me.Save
End Sub