Printing with restrictions - VB
 

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

Printing with restrictions - VB
 

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"pgarcia" wrote in message ...
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

Printing with restrictions - VB
 

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

"pgarcia" wrote in message
...
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

Printing with restrictions - VB
 

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...

In article ,
pgarcia wrote:

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

Printing with restrictions - VB
 

Sorry, that did not work. Please advise. Thanks

"Don Guillett" wrote:

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

"pgarcia" wrote in message
...
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

Printing with restrictions - VB
 

Cool, what if the cells are in different areas, sorry, should have said that
in the first msg.
Cell
K11
K26
D29 ect.

"Ron de Bruin" wrote:

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"pgarcia" wrote in message ...
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

Printing with restrictions - VB
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"pgarcia" wrote in message
...
Sorry, that did not work. Please advise. Thanks

"Don Guillett" wrote:

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

"pgarcia" wrote in message
...
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

Printing with restrictions - VB
 

Take a look at the solution I posted in .programming. Change the range to

...Range("K11,K26,D29")

In article ,
pgarcia wrote:

Cool, what if the cells are in different areas, sorry, should have said that
in the first msg.
Cell
K11
K26
D29 ect.

"Ron de Bruin" wrote:

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"pgarcia" wrote in message
...
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

Printing with restrictions - VB
 

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


"Don Guillett" wrote:


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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"pgarcia" wrote in message
...
Sorry, that did not work. Please advise. Thanks

"Don Guillett" wrote:

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

"pgarcia" wrote in message
...
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

Printing with restrictions - VB
 

end with

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"pgarcia" wrote in message
...
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


"Don Guillett" wrote:


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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"pgarcia" wrote in message
...
Sorry, that did not work. Please advise. Thanks

"Don Guillett" wrote:

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

"pgarcia" wrote in message
...
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