PDA

View Full Version : [SOLVED] Looping through checkboxes in user form



cwb1021
04-25-2017, 06:16 AM
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
04-25-2017, 06:52 AM
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
04-25-2017, 09:01 AM
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