I have a UserForm that has three CheckBoxes and some ComboBoxes.
The plan is that these CheckBoxes can be clicked on or off by the user, and that will trigger a filter in a Worksheet immediately (without having to close the UserForm), so I use the Click event for the CheckBoxes. The UserForm can also be closed at any time (I use a CommandButton that just runs Unload me), and when reopened, the CheckBoxes need to return to their correct status, per the status of the filtered Worksheet. To accomplish this, when the CheckBoxes are clicked, I also update some flags in another Worksheet. The idea is that when the UserForm is opened (event Activate), the flags status will be read, and the CheckBox value will be written to.
The checkbox click event: (I have omitted some code relating to the filtering that is irrelevant to my question).
I do this very successfully with the ComboBoxes.
The problem is that when I change the value of the CheckBox in the UserForm Activate Sub, instead of just changing the value, Excel decides to actually run the Click event Sub. I have tried with event Initialize too btw (I don’t understand the difference), with same results.
I had that problem with the ComboBoxes when I was using the Change event. I changed it to the Click event, and the issue went away. Makes sense: run the sub when you click, but not when the Control changes.
But evidently the Checkbox works differently.
Any help is appreciated.
The plan is that these CheckBoxes can be clicked on or off by the user, and that will trigger a filter in a Worksheet immediately (without having to close the UserForm), so I use the Click event for the CheckBoxes. The UserForm can also be closed at any time (I use a CommandButton that just runs Unload me), and when reopened, the CheckBoxes need to return to their correct status, per the status of the filtered Worksheet. To accomplish this, when the CheckBoxes are clicked, I also update some flags in another Worksheet. The idea is that when the UserForm is opened (event Activate), the flags status will be read, and the CheckBox value will be written to.
VBA Code:
Private Sub UserForm_Activate()
Dim shChoices As Worksheet
Dim i As Integer
Dim rFlag1 As Range
Set shChoices = ThisWorkbook.Sheets("Form Choices")
‘Updating ComboBox values
Me.cbComBox1.Clear
For i = 3 To Application.WorksheetFunction.CountA(shChoices.Cells(1, 8).EntireColumn) + 1
Me.cbComBox1.AddItem shChoices.Cells(i, 8).Value
Next i
‘Updating Checkbox value
Set rFlag1 = shChoices.Columns("J:J").Find(What:="FLAG1", LookIn:=xlValues, LookAt:=xlWhole)
If rFlag1 Is Nothing Then
Me.bxCheckbox1.Value = False
Else
Me.bxCheckbox1.Value = True
End If
End SubThe checkbox click event: (I have omitted some code relating to the filtering that is irrelevant to my question).
VBA Code:
Private Sub bxCheckbox1_Click()
Dim shChoices As Worksheet
Dim sFilter, As String
Set shChoices = ThisWorkbook.Sheets("Form Choices")
'Determine boxes status
If Me. bxCheckbox1.Value = True Then
sFilter = "=FILTER1"
Else
sFilter = ""
End If
End SubI do this very successfully with the ComboBoxes.
The problem is that when I change the value of the CheckBox in the UserForm Activate Sub, instead of just changing the value, Excel decides to actually run the Click event Sub. I have tried with event Initialize too btw (I don’t understand the difference), with same results.
I had that problem with the ComboBoxes when I was using the Change event. I changed it to the Click event, and the issue went away. Makes sense: run the sub when you click, but not when the Control changes.
But evidently the Checkbox works differently.
Any help is appreciated.
