VBA excel Userform checkbox issue

Question

I'm having a small issue I'm hoping you can help with. I have created a userform with a set of check boxes. The idea is you can check box the type of filter/copy/paste and then hit okay and it will run. The problem is that it is running as soon as you check the box, instead of waiting. I'm sure my order is screwed up or I need an if statement but for the life of me I can't figure out how to make it work. I've included the code for the button and the "Okay" and "Cancel" buttons as well.

Any help would be 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

    Select Case True
        Case CheckBox1.Enabled
            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
            Set rang = 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

        Case CheckBox2.Enabled
            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
            Set rang = 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

        Case CheckBox3.Enabled
            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
            Set rang = 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

        Case CheckBox4.Enabled
            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
            Set rang = 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

        Case CheckBox5.Enabled
            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
            Set rang = 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

        Case CheckBox6.Enabled
            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
            Set rang = 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
    End Select
    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

Just as an FYI I did attempt to add an If OkButton then <Code to do> then Else End if and that allowed me to check the box without it running the code but after hitting the okay button it didn't run like I had expected. — Deke
– Deke, Commented Jan 28, 2019 at 19:41

Pᴇʜ · Accepted Answer · 2019-01-29 07:07:40Z

1

You need to put your code that is in the checkbox_click event into the button OK event like the following:

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

    Select Case True
        Case CheckBox1.Value
            'Do something
        Case CheckBox2.Value
            'Do something
        Case CheckBox3.Value
            'Do something
        Case CheckBox4.Value
            'Do something
        Case CheckBox5.Value
            'Do something
        Case CheckBox6.Value
            ActiveSheet.Range("$A$1:$AE$10000").AutoFilter Field:=1, Criteria1:="NR"
            ActiveSheet.Range("$A$1:$AE$10000").AutoFilter Field:=21, Criteria1:="FALSE"

            'Output Paginated Pages (in testing)
            Set sh = Worksheets("Country")
            Set rang = sh.UsedRange.Offset(1, 0)
            On Error Resume Next
            Set rang = 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
    End Select

    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

edited Jan 29, 2019 at 7:07

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

answered Jan 28, 2019 at 19:48

IAmNerd2000

7711 gold badge4 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Deke Over a year ago

Your method makes sense but Getting "Compile error: Method or Data Member not found". Any thoughts?

Deke Over a year ago

Thanks! Figured it out. Needed to change ".checked" to ".enabled" Works perfectly now.

Deke Over a year ago

Actually few issues I'm finding as I'm testing this out. 1. If nothing is checked and you hit okay it still runs checkbox1, and no matter what is checked it runs only checkbox1 code.

IAmNerd2000 Over a year ago

It's actually supposed to be .value. sorry, was in .net mode

Deke Over a year ago

Hey thanks! I was actually trying to type that I found that as a resolution as well. Thanks again for your help everything is now working as intended!

Collectives™ on Stack Overflow

VBA excel Userform checkbox issue

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related