Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Restrictions in format | Excel Discussion (Misc queries) | |||
RESTRICTIONS | Excel Discussion (Misc queries) | |||
Printing with restrictions - VB | Excel Discussion (Misc queries) | |||
How to do look up with restrictions | Excel Discussion (Misc queries) | |||
get_Range Restrictions.. | Excel Programming |