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