How to handle Checkbox value in VBA?

Question

I have the following example:

Private Sub setCheck(ByVal val1 As Boolean, ByVal val2 As Boolean, ByVal val3 As Boolean) With userForm1 .checkboxOne.Value = val1 .checkboxTwo.Value = val2 .checkboxThree.Value = val3 End With End Sub Private Sub checkboxOne_Change() Call setCheck(True, False, False) End Sub Private Sub checkboxTwo_Change() Call setCheck(False, True, False) End Sub Private Sub checkboxThree_Change() Call setCheck(False, False, True) End Sub

It is possible to click a check only in one direction. That means, if I clicked the checkboxOne first time, I don't have the ability to check it again. What I need to do?

Only one OptionButton in a group can be selected at one time, so you don't have to worry about making sure only one is "checked". They're designed for picking a single option out of a group - CheckBoxes aren't. — Comintern
– Comintern, Commented Dec 5, 2016 at 15:57

score 3 · Accepted Answer · 2016-12-05 17:01:35Z

The problem that you have is after a checkbox's value has been initialized setting it's value will cause the checkbox's Click and Change events to fire. This causes an infinite loop which effectively locks the checkboxes.

The way around this is to use a class variable to ignore subsequent calls to setCheck during an update.

Private EditMode As Boolean Private Sub setCheck(ByVal val1 As Boolean, ByVal val2 As Boolean, ByVal val3 As Boolean) If Not EditMode Then EditMode = True With UserForm1 .checkboxone.Value = val1 .checkboxtwo.Value = val2 .checkboxthree.Value = val3 End With EditMode = False End If End Sub Private Sub checkboxOne_Change() setCheck True, False, False End Sub Private Sub checkboxTwo_Change() setCheck False, True, False End Sub Private Sub checkboxThree_Change() setCheck False, False, True End Sub

You don't understand my problem. The user should have the possibility to check one of the checkboxes not only one time. That means for example the user clicks checkboxOne then when he clicks checkboxTwothe first checkbox loses its value (set to false). Well, when the user clicks checkboxOne again then the second checkbox loses its value and the first checkbox gets the value back (set to true).

Martin Dreher · Accepted Answer · 2016-12-05 16:03:46Z

What about something like this?

Private Sub chkbx1_Click() chkbx1.Enabled = False chkbx1.Value = Checked End Sub Private Sub Form_Load() Dim ctrl As Control ' reset it whenever you open the form. this could also be defined separately in the F4-properties of the checkbox For Each ctrl In Me.Controls If TypeName(ctrl) = "CheckBox" Then ctrl.Enabled = True ctrl.Value = False End If Next ctrl End Sub

Not quite sure if you really want the checkbock disabled but with the Checked value per se...

Whenever the user opens the form all checkboxes are set to false because the user need to know which one need to check. And the checkboxes mustn't be disabled.
hm, well, adjusted my code to do just that, however: from your description you want i. enabled, unchecked checkboxes when opening the form and ii. disabled checkboxes once the user clicks a checkbox ONCE. That means: per definition, once it's locked the checkbox can only have .Value = Checked... so is there really a point in locking it?

user3598756 · Accepted Answer · 2016-12-05 17:20:02Z

since you're asking for mutually excluding control, you want Option Buttons

say you place three of them called "OptionButton1", "OptionButton2" and ""OptionButton3" then you will place the following in the Userform code pane:

Option Explicit Private Sub OptionButton1_Click() CheckOBs End Sub Private Sub OptionButton2_Click() CheckOBs End Sub Private Sub OptionButton3_Click() CheckOBs End Sub Sub CheckOBs() Dim iOB As Integer For iOB = 1 To CInt(Right(ActiveControl.Name, 1)) Controls("OptionButton" & iOB).Enabled = False Next iOB End Sub

Collectives™ on Stack Overflow

How to handle Checkbox value in VBA?

3 Answers 3

The way around this is to use a class variable to ignore subsequent calls to setCheck during an update.

1 Comment

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

The way around this is to use a class variable to ignore subsequent calls to setCheck during an update.

1 Comment

2 Comments

Comments

Related