Using ActiveX Checkboxes Conditions to Check or Disable Another Checkbox AND to Populate Column

Question

Fair warning I'm very new to using VBA and I've looked all over this site but I can't seem to a similar question.
I'm hoping it's a pretty easy solution though.

I have these checkboxes 1 - 4

Example of data

Column A (CheckBox 1)	Column B (CheckBox2)	Column C (CheckBox3)	Column E Results
1	4	7	E1
2	5	8	E2
3	6	9	E3

Here's a summary of the necessary steps/ conditions

Checkboxes 1 - 3 affect each other and all three affect checkbox 4

If one checkbox in this set of 3 is checked
then the other two are disabled
the checkbox will select a range in its corresponding column to populate to cells in column E
checkbox 4 is supposed to be disabled
If none of them are checked,
then checkbox 4 is checked
the cells in column E are all 0

So for example what should happen is that if I select CheckBox1 in column A, CheckBox2 ,3 and 4 will disable

The data in column A will transfer to column E

And if CheckBox 1 - 3 are not checked CheckBox4 will enable and automatically be checked while E1:E3 will be 0

Right now,

If I run the code the first time around and checkboxes 1 -3 are not clicked then checkbox 4 is clicked. GOOD!
If I click any of checkbox 1 -3, checkbox 4 is not clicked and it is disabled GOOD!
If I deselect checkbox 1 - 3 checkbox 4 is permanently disabled and does not reselect. BAD!
I can select a new checkbox in 1 - 3 and the other two will still disable and column E will change. GOOD!

here's my code so far

Private Sub CheckBox1_Click() 

If CheckBox1.Value = True Then 
    CheckBox2.Value = False 
    CheckBox2.Enabled = False 
    CheckBox3.Value = False 
    CheckBox3.Enabled = False 
    CheckBox4.Value = False 
    CheckBox4.Enabled = False 
    Range("A1:A3").Value = Range("E1:E3").Value

Else: 
    CheckBox2.Enabled = True 
    CheckBox3.Enabled = True

End If 
End Sub 

Private Sub CheckBox2_Click() 

If CheckBox2.Value = True Then 
    CheckBox1.Value = False 
    CheckBox1.Enabled = False 
    CheckBox3.Value = False 
    CheckBox3.Enabled = False
    CheckBox4.Value = False 
    CheckBox4.Enabled = False 
    Range("B1:B3").Value = Range("E1:E3").Value

Else:
    CheckBox1.Enabled = True 
    CheckBox3.Enabled = True

End If 
End Sub 

Private Sub CheckBox3_Click() 

If CheckBox3.Value = True Then 
    CheckBox1.Value = False 
    CheckBox1.Enabled = False 
    CheckBox2.Value = False 
    CheckBox2.Enabled = False 
    CheckBox4.Value = False 
    CheckBox4.Enabled = False 
    Range("A1:A3").Value = Range("E1:E3").Value

Else: 
    CheckBox1.Enabled = True 
    CheckBox2.Enabled = True

End If 
End Sub 

Private Sub CheckBox9_Click() 

If CheckBox1.Value = False And CheckBox2.Value = False And CheckBox3.Value = False Then 
    CheckBox4.Value = True 
    CheckBox4.Enabled = True 
    Range("E1:E3").Value = 0

Else: 
    CheckBox4.Value = False 
    CheckBox4.Enabled = False

End If 
End Sub

"I tried using a loop code I found online for each sub which didn't work" 
"And then I tried with just looping the end at checkbox 4 
same thing checkbox permanently disabled."

Sub CheckboxLoop() 
 Dim objX As OLEObject 

With ActiveSheet 
 For Each objX In .OLEObjects
End If 
 Next 
 End With 
 End Sub

I also tried deleting the CheckBox4.Enabled = False at various points in the code. Once I deleted it all except last Sub Private Sub CheckBox4_Click() and other times deleted just last sub but it would also just be stuck on disabled. Eventually I deleted all CheckBox4.Enabled = False but of course that means check box 4 won't disable.

Any help would be greatly appreciated.

I have my doubts about your user interface: Why do you want to disable the checkboxes? Let's assume the first checkbox is checked (and all the others are unchecked and disabled). But the user decides that they want to select the 2nd checkbox: As this is disabled, first they need to uncheck the first, which would trigger the 4th checkbox to be set (and write the zeros). Only now the 2nd checkbox can be selected. And if you think carefully: Exactly one of the four checkboxes must be selected at any time: Consider to use radiobuttons instead. — FunThomas
– FunThomas, Commented May 21 at 8:03
So true. I should have put in my example that there is another checkbox that can be selected when two of the three are disabled. I understand how with only the four checkboxes displayed it looks unnecessarily complicated. I appreciate this comment though, gives me a better idea on how to do the "minimal reproducible example" next time around. And I'll post here again if there are issues with the bonus checkbox . — Rach P
– Rach P, Commented May 21 at 17:14
Please note that soon, activeX controls on worksheets will be turned off by default: support.microsoft.com/en-us/office/… — jkpieterse
– jkpieterse, Commented May 22 at 15:44

Black cat · Accepted Answer · 2025-05-21 07:33:51Z

0

This mod doesn't iterate through all checkboxes, only add some lines to the original code.
Checks every click the state of all checkboxes, and if all False then enable them and activate the Checkbox4.
Also there were assignment mismatch of ranges (probably caused by copying).

Private Sub CheckBox1_Click()

If CheckBox1.value = False And CheckBox2.value = False And CheckBox3.value = False Then
    CheckBox4.value = True
    CheckBox4.Enabled = True
    CheckBox2.Enabled = True
    CheckBox3.Enabled = True
    Range("E1:E3").value = 0
ElseIf CheckBox1.value = True Then
    CheckBox2.value = False
    CheckBox2.Enabled = False
    CheckBox3.value = False
    CheckBox3.Enabled = False
    CheckBox4.value = False
    CheckBox4.Enabled = False
    Range("E1:E3").value = Range("A1:A3").value

Else
    CheckBox2.Enabled = True
    CheckBox3.Enabled = True

End If
End Sub

Private Sub CheckBox2_Click()

If CheckBox1.value = False And CheckBox2.value = False And CheckBox3.value = False Then
    CheckBox4.value = True
    CheckBox4.Enabled = True
    CheckBox1.Enabled = True
    CheckBox3.Enabled = True
    Range("E1:E3").value = 0
ElseIf CheckBox2.value = True Then
    CheckBox1.value = False
    CheckBox1.Enabled = False
    CheckBox3.value = False
    CheckBox3.Enabled = False
    CheckBox4.value = False
    CheckBox4.Enabled = False
    Range("E1:E3").value = Range("B1:B3").value

Else
    CheckBox1.Enabled = True
    CheckBox3.Enabled = True

End If
End Sub

Private Sub CheckBox3_Click()

If CheckBox1.value = False And CheckBox2.value = False And CheckBox3.value = False Then
    CheckBox4.value = True
    CheckBox4.Enabled = True
    CheckBox1.Enabled = True
    CheckBox2.Enabled = True
    Range("E1:E3").value = 0
ElseIf CheckBox3.value = True Then
    CheckBox1.value = False
    CheckBox1.Enabled = False
    CheckBox2.value = False
    CheckBox2.Enabled = False
    CheckBox4.value = False
    CheckBox4.Enabled = False
    Range("E1:E3").value = Range("C1:C3").value

Else:
    CheckBox1.Enabled = True
    CheckBox2.Enabled = True

End If
End Sub

Private Sub CheckBox9_Click()

If CheckBox1.value = False And CheckBox2.value = False And CheckBox3.value = False Then
    CheckBox4.value = True
    CheckBox4.Enabled = True
    Range("E1:E3").value = 0

Else:
    CheckBox4.value = False
    CheckBox4.Enabled = False

End If
End Sub

SIDENOTE: The CheckBox9_Click() Sub is not needed afterward.

edited May 21 at 7:33

answered May 21 at 7:12

Black cat

7,6358 gold badges67 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Rach P May 21 at 17:08

Thank youu! This worked as well! Glad to know there are so many different solutions to the same problem. And thanks for catching that checkbox 9. Tried to relabel cells for simplicity and it got a bit wonky.

Rach P Jun 9 at 17:56

I've changed up the formula that results in two checkboxes clicked. But I'm getting a type mismatch error. IF you want to take a stab at it, I'd appreciate it. stackoverflow.com/questions/79656588/…

Black cat Jun 10 at 4:47

@RachP I added a comment to your linked question.

VBasic2008 · Accepted Answer · 2025-05-21 08:28:35Z

0

Use Option (Radio) Buttons when Single Option Required

For your functionality, the best way is to use Option buttons. Observe the simplicity of the code.

Private Sub OptionButton1_Click()
    Range("E1:E3").Value = Range("A1:A3").Value
End Sub
Private Sub OptionButton2_Click()
    Range("E1:E3").Value = Range("B1:B3").Value
End Sub
Private Sub OptionButton3_Click()
    Range("E1:E3").Value = Range("C1:C3").Value
End Sub
Private Sub OptionButton4_Click()
    Range("E1:E3").Value = 0
End Sub

If you insist on check boxes use Form Control check boxes instead.
If you insist on ActiveX Control check boxes, try the following code. Observe in the Immediate window what is being executed with each click of a check box. It appears that when the value of a check box is changed outside of its corresponding events, the Click event is triggered anyway. IMO this shows that these check boxes weren't meant to be used to deal with the required functionality, although this code works since it's not too demanding.
Once a control is disabled, you can't manually do anything with it, so only disable the 4th control.
Remove or out-comment the Debug.Print lines if you still plan to use this.

Private Sub CheckBox1_Click()
    Debug.Print "Running1", CheckBox1, CheckBox2, CheckBox3, CheckBox4
    If CheckBox1.Value Then
        CheckBox2.Value = False
        CheckBox3.Value = False
        CheckBox4.Value = False
        Range("E1:E3").Value = Range("A1:A3").Value
    Else
        CheckBox4.Value = True
        Range("E1:E3").Value = 0
    End If
End Sub

Private Sub CheckBox2_Click()
    Debug.Print "Running2", CheckBox1, CheckBox2, CheckBox3, CheckBox4
    If CheckBox2.Value Then
        CheckBox1.Value = False
        CheckBox3.Value = False
        CheckBox4.Value = False
        Range("E1:E3").Value = Range("B1:B3").Value
    Else
        CheckBox4.Value = True
        Range("E1:E3").Value = 0
    End If
End Sub

Private Sub CheckBox3_Click()
    Debug.Print "Running3", CheckBox1, CheckBox2, CheckBox3, CheckBox4
    If CheckBox3.Value Then
        CheckBox1.Value = False
        CheckBox2.Value = False
        CheckBox4.Value = False
        Range("E1:E3").Value = Range("C1:C3").Value
    Else
        CheckBox4.Value = True
        Range("E1:E3").Value = 0
    End If
End Sub

Private Sub CheckBox4_Click()
    Debug.Print "Running4", CheckBox1, CheckBox2, CheckBox3, CheckBox4
End Sub

edited May 21 at 8:28

answered May 21 at 8:22

VBasic2008

56.8k5 gold badges21 silver badges38 bronze badges

2 Comments

Rach P May 21 at 17:05

Thank you so much. Right now, I'd like to avoid option buttons since I may need to add a check box that could be selected even with the other two in the group disabled. Probably should have put it in my example for clarity. But thank you so much for giving this code. Worked great!

Rach P Jun 9 at 17:56

I've changed up the formula that results in two checkboxes clicked. But I'm getting a type mismatch error. IF you want to take a stab at it, I'd appreciate it. stackoverflow.com/questions/79656588/…

Collectives™ on Stack Overflow

Using ActiveX Checkboxes Conditions to Check or Disable Another Checkbox AND to Populate Column

2 Answers 2

3 Comments

Use Option (Radio) Buttons when Single Option Required

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Use Option (Radio) Buttons when Single Option Required

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related