时间戳

首页 > 解决方案 > VBA用户表单复选框无法识别所有选定的框

excel - VBA用户表单复选框无法识别所有选定的框

问题描述

我设置了一个复选框，在您选择复选框并单击确定后，它应该运行每个复选框的代码。但它只运行第一个复选框然后结束而不移动到下一个... IE 如果我有 NN 和 NC 检查它只会做 NN。

我不确定我的代码中缺少什么，我希望有人能看到我没有的东西。任何帮助将不胜感激！！

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

标签： excelvbauserform

解决方案

像这样的事情应该是一个很好的起点

    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

推荐阅读