Printing with restrictions - VB

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello again,
I looking to do the following; if cell A1, B1, and C1 are blank (no data)
then do not allow to print sheet. Given an error and indicate what cell(s) or
name of cell(s) needs to be filled.

Thanks
 
Hi pgarcia

You can copy this event in the thisworkbook module
http://www.rondebruin.nl/code.htm

It will only print if all three cells have a value

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet
If Application.WorksheetFunction.CountA(.Range("A1:C1")) < 3 Then
MsgBox "Please fill in A1:C1"
Cancel = True
Else
'Allow printing
End If
End With
End Sub
 
Put this in the ThisWorkbook module. Change sheet name to suit.
It accounts for the "dreaded space bar"

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name <> "Sheet26" Then Exit Sub
If Len(Application.Trim(Range("a1"))) < 1 _
Or Len(Application.Trim(Range("b1"))) < 1 _
Or Len(Application.Trim(Range("c1"))) < 1 Then
MsgBox "Fill in a1:c1"
Cancel = True
End If
End Sub
 
See other answers in .programming

Please don't post the same question in multiple groups -it fragments
your answers, and potentially wastes the time of those answering a
question that has already been answered...
 
Sorry, that did not work. Please advise. Thanks

Don Guillett said:
Put this in the ThisWorkbook module. Change sheet name to suit.
It accounts for the "dreaded space bar"

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name <> "Sheet26" Then Exit Sub
If Len(Application.Trim(Range("a1"))) < 1 _
Or Len(Application.Trim(Range("b1"))) < 1 _
Or Len(Application.Trim(Range("c1"))) < 1 Then
MsgBox "Fill in a1:c1"
Cancel = True
End If
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Click to expand...
 
Cool, what if the cells are in different areas, sorry, should have said that
in the first msg.
Cell
K11
K26
D29 ect.
 
It was tested. Did you put in the ThisWorkbook module? Did you change
sheet26 to conform?
Was your focus on the sheet at the time? If not, change to suit your
desires.
As mentioned by JE. Pls post in ONLY ONE group from now on
 
Take a look at the solution I posted in .programming. Change the range to

...Range("K11,K26,D29")
 
It was tested. Did you put in the ThisWorkbook module? YES
Did you change sheet26 to conform? CHANGED IT TO "Receipt to Receipt" AS
THAT IS THE NAME OF THE SHEET
Was your focus on the sheet at the time? If not, change to suit your
desires. SORRY, I'M NOT SURE WHAT THAT MEANS
As mentioned by JE. Pls post in ONLY ONE group from now on
OK, GOT IT!!

THIS IS WHAT I DID (sorry for the CAP, but you can't bold anything here):

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet
If Len(Application.Trim(Range("k7"))) < 1 _
Or Len(Application.Trim(Range("k11"))) < 1 _
Or Len(Application.Trim(Range("k14"))) < 1 _
Or Len(Application.Trim(Range("d26"))) < 1 Then
MsgBox "Pleae make sure that the following has data: Request Date, Mrg.
Approval, Total Amount to be Reallocated: & Receipt Number, thank you."
Cancel = True
End If
End Sub
 
Back
Top