VBA userform Checkbox not recognizing all selected boxes

Question

I've setup a checkbox that after you make your checkbox selection and click okay it should run the code for each checked box. But it only runs the first checked box then ends without moving to the next... IE if I have NN and NC checked it will only do NN.

I'm not sure what I'm missing in my code and I'm hoping someone can see what I'm not. Any help would be greatly appreciated!!

Private Sub CheckBox1_Click()
End Sub

Private Sub CheckBox2_Click()
End Sub

Private Sub CheckBox3_Click()
End Sub

Private Sub CheckBox4_Click()
End Sub

Private Sub CheckBox5_Click()
End Sub

Private Sub CheckBox6_Click()
 End Sub

Public Property Get IsCancelled() As Boolean
IsCancelled = cancelled
End Property

Private Sub OkButton_Click()

Dim sh As Worksheet
Dim rang As Range
Dim c As Control

For Each c In Me.Controls

If TypeOf c Is msforms.CheckBox Then

Select Case c.Name
    Case CheckBox1.Value
        'Report Paginated Pages
        ActiveSheet.Range("$A$1:$AE$10000").AutoFilter Field:=1, Criteria1:="NN"
        ActiveSheet.Range("$A$1:$AE$10000").AutoFilter Field:=21, Criteria1:="FALSE"
        Set sh = Worksheets("Country")
        Set rang = sh.UsedRange.Offset(1, 0)
        On Error Resume Next
        rang.SpecialCells(xlCellTypeVisible).Copy
        Worksheets("PPage").Activate
        Worksheets("PPage").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        Selection.Range("G1:R" & lrow).ClearContents
        Selection.Range("V1:AB" & lrow).Delete
        sh.Activate
        Application.CutCopyMode = False
        Range("A1").Select

        'Remove Working pages
        Worksheets("WPage").Activate
        ActiveSheet.Range("$A$1:$AE$10000").AutoFilter Field:=1, Criteria1:="NN"
        Set sh = Worksheets("WPage")
        Set rang = sh.UsedRange.Offset(1, 0)
        rang.SpecialCells(xlCellTypeVisible).Select
        Selection.Delete
        ActiveSheet.Range("$A$1:$AE$10000").AutoFilter Field:=1
        Worksheets("Country").Activate

    Case CheckBox2.Value
        'Report Paginated Pages
        ActiveSheet.Range("$A$1:$AE$10000").AutoFilter Field:=1, Criteria1:="NC"
        ActiveSheet.Range("$A$1:$AE$10000").AutoFilter Field:=21, Criteria1:="FALSE"
        Set sh = Worksheets("Country")
        Set rang = sh.UsedRange.Offset(1, 0)
        On Error Resume Next
        rang.SpecialCells(xlCellTypeVisible).Copy
        Worksheets("PPage").Activate
        Worksheets("PPage").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        Selection.Range("G1:R" & lrow).ClearContents
        Selection.Range("V1:AB" & lrow).Delete
        sh.Activate
        Application.CutCopyMode = False
        Range("A1").Select

        'Remove Working pages
        Worksheets("WPage").Activate
        ActiveSheet.Range("$A$1:$AE$10000").AutoFilter Field:=1, Criteria1:="NC"
        Set sh = Worksheets("WPage")
        Set rang = sh.UsedRange.Offset(1, 0)
        Set rang = rang.SpecialCells(xlCellTypeVisible).Select
        Selection.Delete
        ActiveSheet.Range("$A$1:$AE$10000").AutoFilter Field:=1
        Worksheets("Country").Activate

    Case CheckBox3.Value
        'Report Paginated Pages
        ActiveSheet.Range("$A$1:$AE$10000").AutoFilter Field:=1, Criteria1:="NF"
        ActiveSheet.Range("$A$1:$AE$10000").AutoFilter Field:=21, Criteria1:="FALSE"
        Set sh = Worksheets("Country")
        Set rang = sh.UsedRange.Offset(1, 0)
        On Error Resume Next
        rang.SpecialCells(xlCellTypeVisible).Copy
        Worksheets("PPage").Activate
        Worksheets("PPage").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        Selection.Range("G1:R" & lrow).ClearContents
        Selection.Range("V1:AB" & lrow).Delete
        sh.Activate
        Application.CutCopyMode = False
        Range("A1").Select

        'Remove Working pages
        Worksheets("WPage").Activate
        ActiveSheet.Range("$A$1:$AE$10000").AutoFilter Field:=1, Criteria1:="NF"
        Set sh = Worksheets("WPage")
        Set rang = sh.UsedRange.Offset(1, 0)
        Set rang = rang.SpecialCells(xlCellTypeVisible).Select
        Selection.Delete
        ActiveSheet.Range("$A$1:$AE$10000").AutoFilter Field:=1
        Worksheets("Country").Activate

    Case CheckBox4.Value
        'Report Paginated Pages
        ActiveSheet.Range("$A$1:$AE$10000").AutoFilter Field:=1, Criteria1:="NT"
        ActiveSheet.Range("$A$1:$AE$10000").AutoFilter Field:=21, Criteria1:="FALSE"
        Set sh = Worksheets("Country")
        Set rang = sh.UsedRange.Offset(1, 0)
        On Error Resume Next
        rang.SpecialCells(xlCellTypeVisible).Copy
        Worksheets("PPage").Activate
        Worksheets("PPage").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        Selection.Range("G1:R" & lrow).ClearContents
        Selection.Range("V1:AB" & lrow).Delete
        sh.Activate
        Application.CutCopyMode = False
        Range("A1").Select

        'Remove Working pages
        Worksheets("WPage").Activate
        ActiveSheet.Range("$A$1:$AE$10000").AutoFilter Field:=1, Criteria1:="NT"
        Set sh = Worksheets("WPage")
        Set rang = sh.UsedRange.Offset(1, 0)
        Set rang = rang.SpecialCells(xlCellTypeVisible).Select
        Selection.Delete
        ActiveSheet.Range("$A$1:$AE$10000").AutoFilter Field:=1
        Worksheets("Country").Activate

    Case CheckBox5.Value
        'Report Paginated Pages
        ActiveSheet.Range("$A$1:$AE$10000").AutoFilter Field:=1, Criteria1:="NB"
        ActiveSheet.Range("$A$1:$AE$10000").AutoFilter Field:=21, Criteria1:="FALSE"
        Set sh = Worksheets("Country")
        Set rang = sh.UsedRange.Offset(1, 0)
        On Error Resume Next
        rang.SpecialCells(xlCellTypeVisible).Copy
        Worksheets("PPage").Activate
        Worksheets("PPage").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        Selection.Range("G1:R" & lrow).ClearContents
        Selection.Range("V1:AB" & lrow).Delete
        sh.Activate
        Application.CutCopyMode = False
        Range("A1").Select

        'Remove Working pages
        Worksheets("WPage").Activate
        ActiveSheet.Range("$A$1:$AE$10000").AutoFilter Field:=1, Criteria1:="NB"
        Set sh = Worksheets("WPage")
        Set rang = sh.UsedRange.Offset(1, 0)
        Set rang = rang.SpecialCells(xlCellTypeVisible).Select
        Selection.Delete
        ActiveSheet.Range("$A$1:$AE$10000").AutoFilter Field:=1
        Worksheets("Country").Activate

    Case CheckBox6.Value
        'Report Paginated Pages
        ActiveSheet.Range("$A$1:$AE$10000").AutoFilter Field:=1, Criteria1:="NR"
        ActiveSheet.Range("$A$1:$AE$10000").AutoFilter Field:=21, Criteria1:="FALSE"
        Set sh = Worksheets("Country")
        Set rang = sh.UsedRange.Offset(1, 0)
        On Error Resume Next
        rang.SpecialCells(xlCellTypeVisible).Copy
        Worksheets("PPage").Activate
        Worksheets("PPage").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        Selection.Range("G1:R" & lrow).ClearContents
        Selection.Range("V1:AB" & lrow).Delete
        sh.Activate
        Application.CutCopyMode = False
        Range("A1").Select

        'Remove Working pages
        Worksheets("WPage").Activate
        ActiveSheet.Range("$A$1:$AE$10000").AutoFilter Field:=1, Criteria1:="NR"
        Set sh = Worksheets("WPage")
        Set rang = sh.UsedRange.Offset(1, 0)
        Set rang = rang.SpecialCells(xlCellTypeVisible).Select
        Selection.Delete
        ActiveSheet.Range("$A$1:$AE$10000").AutoFilter Field:=1
        Worksheets("Country").Activate
    Case Else
    End Select
End If
Next c
    Hide
End Sub

Private Sub CancelButton_Click()
OnCancel
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = VbQueryClose.vbFormControlMenu Then
    Cancel = True
    OnCancel
End If
End Sub

Private Sub OnCancel()
cancelled = True
Hide
End Sub

Remove all `On Error Resume Next`. The way you use it just hides **all** error messages until `End Sub` but the errors still occur, you just cannot see their messages. If you cannot see the errors you cannot fix them, if you don't fix them your code doesn't work. Remove `On Error Resume Next` completely and [fix your errors](https://excelmacromastery.com/vba-error-handling). • You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Pᴇʜ, Jan 29 '19 at 16:02
You are using select as @JvdV said, you need to loop them, or just get the values, in a similar way but using `if` and `elseif` and `end if` — Nathan_Sav, Jan 29 '19 at 16:12
@PEH thanks for the advice. I was using the on error resume to mainly hide the errors given by clearcontents and delete lines since I have them hidden right now and not everyone who uses this will have those columns hidden. Also I understand most peoples hatred for .select but since I'm still learning (and self taught) I use the selections as a way of keeping track of what I'm doing, and then go back and clean it up after I've got everything working right. But I appreciate your input! — Deke, Jan 29 '19 at 16:14
JvdV and Nathan_Sav thanks guys! that makes sense. I'll start playing around with looping or using IF, elseif etc... I appreciate the help! — Deke, Jan 29 '19 at 16:16
@Deke it's not hatred, it's just a really, really bad way to learn how to debug. Instead, learn to use breakpoints (F9), the *immediate* (Ctrl+G) toolwindow (together with `Debug.Print` statements), step-through (F8), and the *locals* toolwindow. Thank yourself later! (I'm self-taught too - don't ever let that stop you from anything!) — Mathieu Guindon, Jan 29 '19 at 16:21

Nathan_Sav · Accepted Answer · 2019-01-29T16:52:05.427

2

Something like this should be a good start point

    Private Sub CommandButton1_Click()

Dim c As Control

For Each c In Me.Controls

    If TypeOf c Is msforms.CheckBox Then

    If c Then

        Select Case c.Name

            Case "CheckBox1"

                MsgBox "Checkbox 1"

            Case "CheckBox2"

                MsgBox "Checkbox 2"

            Case Else

        End Select

    End If

    End If

Next c

End Sub

edited Jan 29 '19 at 16:52

answered Jan 29 '19 at 16:18

Nathan_Sav

8,466
2
13
20

This is a great start thanks! I've got it in and it's doing a bit of an infinite loop between the checkboxes whether they are checked or not, it's also not actually running the code for any of them. I am playing around with it though and I really appreciate your help! – Deke Jan 29 '19 at 16:38
No probs, you need to add your original code in to. So perhaps before the select case, you could say `if c then` this will only check checked check boxes. – Nathan_Sav Jan 29 '19 at 16:42
I've updated the code to reflect how I set it up with your loop suggestion. Should I enter the `if c then` to each individual `Case` line or should I place it someplace else? – Deke Jan 29 '19 at 16:48
No, before the select case statement, you only need to do once, in each select will be redundant code. – Nathan_Sav Jan 29 '19 at 16:51
Makes total sense. Made the correction. Also it would help if I had put quotes on each of the `Checkbox#` once I did that it ran perfectly. Thanks for all your help!!!! – Deke Jan 29 '19 at 17:02

VBA userform Checkbox not recognizing all selected boxes

1 Answers1