Looping through checkboxes in user form

[cwb1021]

Good morning experts,

I'm trying to write a procedure that can loop through all selected checkboxes in a userform and if selected, hide rows in the worksheet. So I've started by trying to get just one checkbox to hide rows with the below code. This does not give me any errors, but does not do anything either.

So for example, I

There are 3 checkboxes in the userform: checkbox 1 is labeled "1", checkbox 2 is labeled "1.5", and checkbox 3 is labeled "2"

If checkbox 1 and checkbox 2 are selected, I would like for all rows in the worksheet whose value in column AM is not equal to "1" or "1.5" to be hidden. And, if no checkboxes are selected, no rows would be hidden.

Private Sub FilterButton1_Click()
Dim Checked As Control
Dim LengthRange As Range, LengthCell As Range
Dim x As Long, y As Long, z As Long
Set LengthRange = Sheets("Sheet1").Range(("AM2"), Range("AM2").End(xlDown))
x = 1
y = 1.5
z = 2

For Each Checked In UserForm1.Controls
   If TypeName(Checked) = "Checkbox" Then
      If Checked.Value = True Then
         For Each LengthCell In LengthRange
            If LengthCell.Value <> x Then
                     LengthCell.EntireRow.Hide
            End If
         Next LengthCell
      End If
   End If
Next Checked
End Sub

Any suggestions on how to do this?

Thanks!

Chris

[GTO]

Greetings,

Your first two issues are:

If TypeName(Checked) = "CheckBox" Then

In comparing equality, the comparison is case sensitive. You can U/LCase both sides or ensure that you enter the value being looked for exactly right.

The next issue is:


LengthCell.EntireRow.Hidden = True




Hidden is a Property and there is no "Hide" method.

Hope that helps,

Mark

[Paul_Hossler]

Also y is Dim-ed as Long, so it won't like 1.5

I don't think you really need to loop the CheckBoxes since there's only a few. I'd keep it simple

Userform code

Option Explicit

Dim bInit As Boolean

Private Sub CheckBox1_Change()
    If Not bInit Then FilterData
End Sub

Private Sub CheckBox2_Change()
    If Not bInit Then FilterData
End Sub

Private Sub CheckBox3_Change()
    If Not bInit Then FilterData
End Sub

Private Sub ExitButton_Click()
    Me.Hide
    Unload Me
End Sub

Private Sub UserForm_Initialize()
    
    bInit = True
    
    With Sheets("Sheet1")
        .Range(("AM2"), Range("AM2").End(xlDown)).EntireRow.Hidden = False
    End With
    
    Application.EnableEvents = False
    Me.CheckBox1 = True
    Me.CheckBox2 = True
    Me.CheckBox3 = True
    Application.EnableEvents = True
    
    bInit = False
    
End Sub

Standard Module

Option Explicit
Sub ShowTheForm()
    Load UserForm1
    UserForm1.Show vbModeless
End Sub

Sub FilterData()
    Dim LengthRange As Range, LengthCell As Range
    With Sheets("Sheet1")
        Set LengthRange = .Range(("AM2"), Range("AM2").End(xlDown))
    End With
    
    Application.ScreenUpdating = False
    For Each LengthCell In LengthRange.Cells
       Select Case LengthCell.Value
            Case 1
                LengthCell.EntireRow.Hidden = Not UserForm1.CheckBox1
            Case 1.5
                LengthCell.EntireRow.Hidden = Not UserForm1.CheckBox2
            Case 2
                LengthCell.EntireRow.Hidden = Not UserForm1.CheckBox3
        End Select
    Next LengthCell
    Application.ScreenUpdating = True
End Sub