CheckBox as a condition

Question

I am trying to use ActiveX check box to filter which lines of the code will be executed, but it thows "400" error, also from some reason I can not uncheck these checkboxes, can someone help please?

.
.
.
If CheckBox1.Value = True Then
     Columns("A:F").AutoFilter Field:=1, Criteria1:=">11999", Operator:=xlAnd, Criteria2:="<13000"
     ActiveSheet.PrintOut
End If

If CheckBox2.Value = True Then
     Columns("A:F").AutoFilter Field:=1, Criteria1:=">12999", Operator:=xlAnd, Criteria2:="<14000"
     ActiveSheet.PrintOut
End If
.
.
.

EDIT (whole code - there are going to be way more same conditions with just different checkbox and different range):

Sub TISK_IV_OC()

    Sheets("TISK IV OC").Activate

    Dim ws As Worksheet
    Set ws = ActiveSheet

    If ws.Range("A1") = "" Then
        MsgBox ("Neni co tisknout › nejdrive preved data!")
        Exit Sub
    End If

    Sheets("TISK IV OC").PageSetup.CenterFooter = "&""Calibri,Bold""&18 " & "IV OC: " & Format(Date + 1, "dd.mm.yyyy")

    Application.PrintCommunication = False
    With Sheets("TISK IV OC").PageSetup
        .FitToPagesWide = 1
        .FitToPagesTall = False
    End With
    Application.PrintCommunication = True

    Dim x As Integer
    For x = 1 To 2
        Sheets("TISK IV OC").PageSetup.CenterHeader = "&""Calibri,Bold""&18 " & x & " . KOLO"

        If ws.CheckBox1.Value = True Then
            Debug.Print ws.CheckBox1.Value
            ws.Columns("A:F").AutoFilter Field:=1, Criteria1:=">11999", Operator:=xlAnd, Criteria2:="<13000"
            ws.PrintOut
        End If

        If ws.CheckBox2.Value = True Then
            Debug.Print ws.CheckBox1.Value
            ws.Columns("A:F").AutoFilter Field:=1, Criteria1:=">12999", Operator:=xlAnd, Criteria2:="<14000"
            ws.PrintOut
        End If
     Next x

    ActiveSheet.ShowAllData
    ws.Range("A1").Select

End Sub

Private Sub CheckBox1_Click()

End Sub

Using `Columns("A:F")...` without specifying the worksheet can lead to problems. use 'ws.Columns("A:F")...' Always specify the worksheet explicitly. Otherwise we assume you're a noob and confused. Your code won't work when you start changing sheets. — D_Bester, Nov 15 '18 at 14:35
Regarding the unchecking issue, Active X controls are generally considered buggier than Form controls so the latter might be preferred — Kubie, Nov 15 '18 at 15:06
D_Bester: I have started with VBA recently, so I guess, I can be considerd as noob. But I am learning. However, thank you for you advise. Kubie: Thank you, I will remember this — Vratislav Vašina, Nov 15 '18 at 15:34

D_Bester · Accepted Answer · 2018-11-15T15:59:41.383

0

You need to specify the worksheet. Best to use the code name for the sheet shown in the properties in parenthesis "(Name)".

If Sheet1.CheckBox1.Value = True Then
    Debug.Print Sheet1.CheckBox1.Value
    ActiveSheet.Columns("A:F").AutoFilter Field:=1, Criteria1:=">11999", Operator:=xlAnd, Criteria2:="<13000"
    ActiveSheet.PrintOut
End If

Using code name

https://wellsr.com/vba/2015/excel/complete-guide-to-excel-vba-ActiveX-checkboxes/#value

edited Nov 15 '18 at 15:59

answered Nov 15 '18 at 14:29

D_Bester

5,723
5
35
77

I used your code, but now it shows following error: Compile error: Method or data member not found.. Any ideas what have I done wrong? – Vratislav Vašina Nov 15 '18 at 15:30
It means the compiler can't find match the CheckBox1 to the worksheet. Using the code name should eliminate the problem assuming you actually have a CheckBox1 on the given sheet. – D_Bester Nov 15 '18 at 16:04
The Sub code is: Private Sub CheckBox1_Click()... I tryied "CheckBox1" and also "CheckBox1_Click" just to be sure, but non of it works. To get to the code I use right click to checkbox to ensure I use the right one but still nothing.. Obviously I am missing something but can not figure out what it is.. I will edit the question with whole Sub.. Maybe you will see something – Vratislav Vašina Nov 15 '18 at 16:33
Do this: `Debug.Print Sheets("TISK IV OC").CodeName` to give you the code name. Then use that instead of ws in `ws.CheckBox1` – D_Bester Nov 15 '18 at 16:49

CheckBox as a condition

1 Answers1