Search code examples
excelvbauserform

VBA excel Userform checkbox issue

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
Solution

  • 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