Preventing Printing with Check Boxes

Evan1

New Member
Joined
Jun 1, 2018
Messages
8
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...

Code:
[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.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Try something like this

In standard module declare a public boolean variable at top of module
Code:
Public CanPrint As Boolean

In ThisWorkbook module
Code:
Private Sub Workbook_Open()
    CanPrint = False
End Sub

In UserForm Code when conditions are satisfied
Code:
   CanPrint = True

In Workbook_BeforePrint
Code:
   If CanPrint = TRUE Then
.... print instructions
   Else
     Cancel=True
   End If
 
Last edited:
Upvote 0
I'm not sure I follow. What is the UserForm Code?

Is there an explanation why my code won't work?
 
Upvote 0
What is the UserForm Code?
I am puzzled :confused:
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 :eeek:


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
Code:
MsgBox Sheets("Sheet1").CheckBoxes("Check Box 161").Value
MsgBox Sheets("Sheet1").CheckBoxes("Check Box 4").Value
 
Last edited:
Upvote 0
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
Code:
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?
 
Upvote 0
{alt } {F11} takes you to VBA
Insert a new module (right click on ThisWorbook to get the menu) and place it there like this

Code:
Sub Values()
MsgBox ...etc

End Sub
 
Upvote 0
Going back to your original code ...

This should work for you
Code:
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
Code:
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
Code:
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
Code:
' 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
 
Upvote 0

Forum statistics

Threads
1,224,817
Messages
6,181,148
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top