CheckBox in UserForm not working

cfba50

New Member
Joined
Jul 30, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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.

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 Sub

The 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 Sub

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.
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,728
Office Version
  1. 365
Platform
  1. Windows
Try using the AfterUpdate option for the checkbox.
 
Solution

cfba50

New Member
Joined
Jul 30, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
The AfterUpdate event appears to resolve the issue
Thanks!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,728
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

Forum statistics

Threads
1,147,734
Messages
5,742,861
Members
423,760
Latest member
photogfrog

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top