convert macro to single sheet

Smurfit-Stone

Active Member
Joined
Dec 1, 2004
Messages
485
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
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try this - change Sheet1 if necessary

Rich (BB code):
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

Active Member
Joined
Dec 1, 2004
Messages
485
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

Legend
Joined
Jun 19, 2002
Messages
63,650
Did your previous code work?

Try this

Rich (BB code):
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,233
Messages
5,594,963
Members
413,954
Latest member
mrsandy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top