Preventing Printing with Check Boxes

[Evan1]

Hello,

I have an excel sheet that has multiple line items that a user needs to check a "Required" checkbox or a "N/A" checkbox. If the "Required" checkbox is selected, then the user either needs to select a "Completed" checkbox OR fill in a comment in a cell to explain why it is not yet completed. All of the checkboxes are Form Control checkboxes and not ActiveX Control checkboxes.

I would like to prevent the user from being able to print the form unless it is filled out correctly. Basically, if the "Required" checkbox ("Check Box 161") is selected AND the "Completed" checkbox ("Check Box 4") is deselected AND there is no comment in cell J9, then cancel printing. So far, the code I have under the ThisWorkbook section is...

[COLOR=#333333]Private Sub Workbook_BeforePrint(Cancel As Boolean)[/COLOR]

[COLOR=#333333]If Sheets("Sheet1").CheckBoxes("Check Box 161").Value = 1 And Sheets("Sheet1").CheckBoxes("Check Box 4").Value = 0 And Sheets("Sheet1").Range("J9").Value = "" Then[/COLOR]
[COLOR=#333333]Cancel = True[/COLOR]
[COLOR=#333333]MsgBox ("Please Insert Comment on Line 9")[/COLOR]
[COLOR=#333333]End If[/COLOR]

[COLOR=#333333]End Sub[/COLOR]

I have changed the name of the sheet to sheet1 for this example, but I have the real sheet name in my code. I can get this code to work with just the J9 value section, but it won't work with form control checkbox codes. I can get it to work if I were to insert in a new ActiveX control checkboxes and use those proper names, but I would prefer not to change all of my boxes to ActiveX boxes because that will be quite a bit of code for me to change.

 

[Yongle]

Try something like this

In standard module declare a public boolean variable at top of module

Public CanPrint As Boolean

In ThisWorkbook module

Private Sub Workbook_Open()
    CanPrint = False
End Sub

In UserForm Code when conditions are satisfied

   CanPrint = True

In Workbook_BeforePrint

   If CanPrint = TRUE Then
.... print instructions
   Else
     Cancel=True
   End If

 

[Evan1]

I'm not sure I follow. What is the UserForm Code?

Is there an explanation why my code won't work?

 

[Yongle]

What is the UserForm Code?

I am puzzled

I would like to prevent the user from being able to print the form

Is your form a UserForm (created in VBA window) OR formatted cells in the workbook itself? - I assumed the former

Is there an explanation why my code won't work?

Check Box 4 value is probably -4196, but you are testing for a zero

Check the values of your boxes with

MsgBox Sheets("Sheet1").CheckBoxes("Check Box 161").Value
MsgBox Sheets("Sheet1").CheckBoxes("Check Box 4").Value

 

[Evan1]

Sorry - my fault. When I said "I would like to prevent the user from being able to print the form", I meant I would like to prevent the user from printing Sheet1 in the workbook, unless certain items are filled out properly on Sheet1.

Check the values of your boxes with

MsgBox Sheets("Sheet1").CheckBoxes("Check Box 161").Value
MsgBox Sheets("Sheet1").CheckBoxes("Check Box 4").Value

Again, I apologize as I am relatively new to this, where would I type this code to see the value? Where would the value of the box show?

 

[Yongle]

{alt } {F11} takes you to VBA
Insert a new module (right click on ThisWorbook to get the menu) and place it there like this

Sub Values()
MsgBox ...etc

End Sub

 

[Yongle]

Going back to your original code ...

This should work for you

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    If Sheets("Sheet1").CheckBoxes("Check Box 161").Value = 1 And Sheets("Sheet1").CheckBoxes("Check Box 4").Value <> 1 And Sheets("Sheet1").Range("J9").Value = "" Then
        Cancel = True
        MsgBox ("Please Insert Comment on Line 9")
    End If
End Sub

To avoid repeating the sheet name

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    With Sheets("Sheet1")
        If .CheckBoxes("Check Box 161").Value = 1 And .CheckBoxes("Check Box 4").Value <> 1 And .Range("J9").Value = "" Then
            Cancel = True
            MsgBox ("Please Insert Comment on Line 9")
        End If
    End With
End Sub

The Value property is generally presumed by VBA, and in this case, this should also work for you

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    With Sheets("Sheet1")
        If .CheckBoxes("Check Box 161") = 1 And .CheckBoxes("Check Box 4") <> 1 And .Range("J9") = "" Then
            Cancel = True
            MsgBox ("Please Insert Comment on Line 9")
        End If
    End With
End Sub

Checkbox numbers do not tell you a lot when the code needs amending in 6 months time (especially if you have a lot of them and have changed the text to for the benefit of the form.) Even finding Check Box 161 on your worksheet can be tricky if they were moved around. Renaming each one is a pain. Consider putting a note in your code so that it is obvious which check box you are referring to

' if user has green eyes AND does not have 2 heads AND did not explain why THEN
If .CheckBoxes("Check Box 161") = 1 And .CheckBoxes("Check Box 4") <> 1 And .Range("J9") = "" Then

 

[Evan1]

Perfect! That first code worked! Thank you so much for your help!