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
 

Please post in one group

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
 

One way:

Put this in your ThisWorkbook code module:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With Sheets("Sheet1").Range("A1:C1")
Cancel = Application.CountA(.Cells) < .Cells.Count
If Cancel Then _
MsgBox "You need to fill in cells " & _
.Cells.Address(False, False) & _
" of worksheet " & .Parent.Name & " before printing!"
End With
End Sub


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
 

Thank you sir. Can the Vb code bring up the name of the cell and not the
address? Or, could it bring up text from a different cell. I have it like this

Request Date (H7) and the cell that need to be field is K7.


"JE McGimpsey" wrote:

One way:

Put this in your ThisWorkbook code module:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With Sheets("Sheet1").Range("A1:C1")
Cancel = Application.CountA(.Cells) < .Cells.Count
If Cancel Then _
MsgBox "You need to fill in cells " & _
.Cells.Address(False, False) & _
" of worksheet " & .Parent.Name & " before printing!"
End With
End Sub


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
 

JE, I seem to have found a problem. I'm not getting the form back form the
field and I noted that on the fields that I wanted filled out is not being
filled out. This is what I found, when the user gets the msg box and click
"ok", then can go back to the field type something there and then remove it
(I'm not sure how there doing this) and they can then print the form. Please
advise. Thank
VB code

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With Sheets("Receipt to Receipt").Range("K7")
Cancel = Application.CountA(.Cells) < .Cells.Count
If Cancel Then _
MsgBox "You need a date in 'Request Date' field before printing!"
End With

With Sheets("Receipt to Receipt").Range("K11")
Cancel = Application.CountA(.Cells) < .Cells.Count
If Cancel Then _
MsgBox "You need to choose a Mgr. from the 'Mrg. Approval' drop down
list before printing!"
End With

With Sheets("Receipt to Receipt").Range("K14")
Cancel = Application.CountA(.Cells) < .Cells.Count
If Cancel Then _
MsgBox "You need an amount in the 'Total Amount to be Reallocated'
field before printing!"
End With

With Sheets("Receipt to Receipt").Range("D26")
Cancel = Application.CountA(.Cells) < .Cells.Count
If Cancel Then _
MsgBox "You need a number in the 'Receipt Number' before printing!"
End With

End Sub

"JE McGimpsey" wrote:

One way:

Put this in your ThisWorkbook code module:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With Sheets("Sheet1").Range("A1:C1")
Cancel = Application.CountA(.Cells) < .Cells.Count
If Cancel Then _
MsgBox "You need to fill in cells " & _
.Cells.Address(False, False) & _
" of worksheet " & .Parent.Name & " before printing!"
End With
End Sub


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
 

Since you're checking individual cells, this might be easier:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim vCheck As Variant
Dim i As Long
Dim nAddr As Long
Dim nMsg As Long

vCheck = Array( _
Array("K7", "a date in 'Request Date' field"), _
Array("K11", _
"to choose a Mgr. from the 'Mgr. Approval' dropdown list"), _
Array("K14", _
"an amount in the 'Total Amount to be Reallocated' field"), _
Array("D26", "a number in the 'Receipt Number'"))

nAddr = LBound(vCheck)
nMsg = nAddr + 1

With Sheets("Receipt to Receipt")
For i = LBound(vCheck) To UBound(vCheck)
Cancel = Trim(.Range(vCheck(i)(nAddr)).Text) = vbNullString
If Cancel Then
MsgBox "You need " & vCheck(i)(nMsg) & " before printing!"
Exit For
End If
Next i
End With
End Sub


In article ,
pgarcia wrote:

JE, I seem to have found a problem. I'm not getting the form back form the
field and I noted that on the fields that I wanted filled out is not being
filled out. This is what I found, when the user gets the msg box and click
"ok", then can go back to the field type something there and then remove it
(I'm not sure how there doing this) and they can then print the form. Please

Printing with restrictions - VB
 

Works like a charm. Thanks!!

"JE McGimpsey" wrote:

Since you're checking individual cells, this might be easier:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim vCheck As Variant
Dim i As Long
Dim nAddr As Long
Dim nMsg As Long

vCheck = Array( _
Array("K7", "a date in 'Request Date' field"), _
Array("K11", _
"to choose a Mgr. from the 'Mgr. Approval' dropdown list"), _
Array("K14", _
"an amount in the 'Total Amount to be Reallocated' field"), _
Array("D26", "a number in the 'Receipt Number'"))

nAddr = LBound(vCheck)
nMsg = nAddr + 1

With Sheets("Receipt to Receipt")
For i = LBound(vCheck) To UBound(vCheck)
Cancel = Trim(.Range(vCheck(i)(nAddr)).Text) = vbNullString
If Cancel Then
MsgBox "You need " & vCheck(i)(nMsg) & " before printing!"
Exit For
End If
Next i
End With
End Sub


In article ,
pgarcia wrote:

JE, I seem to have found a problem. I'm not getting the form back form the
field and I noted that on the fields that I wanted filled out is not being
filled out. This is what I found, when the user gets the msg box and click
"ok", then can go back to the field type something there and then remove it
(I'm not sure how there doing this) and they can then print the form. Please

Printing with restrictions - VB
 

Sir, could you help me with one more thing? Thanks

I added so more code to it. Where I remove the cell color, I want to put it
back the way it was after it prints. Thanks

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim vCheck As Variant
Dim i As Long
Dim nAddr As Long
Dim nMsg As Long

vCheck = Array( _
Array("K7", "a date in 'Request Date' field"), _
Array("K11", _
"to choose a Mgr. from the 'Mgr. Approval' dropdown list"), _
Array("K14", _
"an amount in the 'Total Amount to be Reallocated' field"), _
Array("D26", "a number in the 'Receipt Number'"))

nAddr = LBound(vCheck)
nMsg = nAddr + 1

With Sheets("Receipt to Receipt")
For i = LBound(vCheck) To UBound(vCheck)
Cancel = Trim(.Range(vCheck(i)(nAddr)).Text) = vbNullString
If Cancel Then
MsgBox "You need " & vCheck(i)(nMsg) & " before printing!"
Exit For
End If
Next i

Sheets("Receipt to Receipt").Unprotect Password:="autpbg1"
Range("N5:S5").Select
Selection.Interior.ColorIndex = xlNone
Range("G6:S15").Select
Selection.Interior.ColorIndex = xlNone
Range("A18:C18").Select
Selection.Interior.ColorIndex = xlNone
Range("A19:S48").Select
Selection.Interior.ColorIndex = xlNone
End With
End Sub
Sub colorbefor() <--- this part is what I'm having problems with.
Range("N5:S5").Select<--- I'm not sure if it goies in the right
place here.
Selection.Interior.ColorIndex = 37
Range("G6:S15").Select
Selection.Interior.ColorIndex = 37
Range("A18:C18").Select
Selection.Interior.ColorIndex = 37
Range("A19:S48").Select
Selection.Interior.ColorIndex = 37
Sheets("Receipt to Receipt").Protect Password:="autpbg1"
End Sub



"pgarcia" wrote:

Works like a charm. Thanks!!

"JE McGimpsey" wrote:

Since you're checking individual cells, this might be easier:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim vCheck As Variant
Dim i As Long
Dim nAddr As Long
Dim nMsg As Long

vCheck = Array( _
Array("K7", "a date in 'Request Date' field"), _
Array("K11", _
"to choose a Mgr. from the 'Mgr. Approval' dropdown list"), _
Array("K14", _
"an amount in the 'Total Amount to be Reallocated' field"), _
Array("D26", "a number in the 'Receipt Number'"))

nAddr = LBound(vCheck)
nMsg = nAddr + 1

With Sheets("Receipt to Receipt")
For i = LBound(vCheck) To UBound(vCheck)
Cancel = Trim(.Range(vCheck(i)(nAddr)).Text) = vbNullString
If Cancel Then
MsgBox "You need " & vCheck(i)(nMsg) & " before printing!"
Exit For
End If
Next i
End With
End Sub


In article ,
pgarcia wrote:

JE, I seem to have found a problem. I'm not getting the form back form the
field and I noted that on the fields that I wanted filled out is not being
filled out. This is what I found, when the user gets the msg box and click
"ok", then can go back to the field type something there and then remove it
(I'm not sure how there doing this) and they can then print the form. Please

Printing with restrictions - VB
 

Perhaps

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Const sPWORD As String = "autpbg1"
Dim ws As Worksheet
Dim vCheck As Variant
Dim i As Long
Dim nAddr As Long
Dim nMsg As Long

vCheck = Array( _
Array("K7", "a date in 'Request Date' field"), _
Array("K11", _
"to choose a Mgr. from the 'Mgr. Approval' dropdown list"), _
Array("K14", _
"an amount in the 'Total Amount to be Reallocated' field"), _
Array("D26", "a number in the 'Receipt Number'"))

nAddr = LBound(vCheck)
nMsg = nAddr + 1

With Sheets("Receipt to Receipt")
For i = LBound(vCheck) To UBound(vCheck)
Cancel = Trim(.Range(vCheck(i)(nAddr)).Text) = vbNullString
If Cancel Then
MsgBox "You need " & vCheck(i)(nMsg) & _
" before printing!"
Exit For
End If
Next i
If Not Cancel Then
.Unprotect Password:=sPWORD
With .Range("A18:C18, A19:S48, G6:S15,N5:S5")
.Interior.ColorIndex = xlColorIndexNone
On Error Resume Next
Application.EnableEvents = False
.Parent.PrintOut
Application.EnableEvents = True
On Error GoTo 0
.Interior.ColorIndex = 37
End With
.Protect Password:=sPWORD
End If
End With
Cancel = True
End Sub



In article ,
pgarcia wrote:

Sir, could you help me with one more thing? Thanks