How to enable/disable CommandButtons by using checkboxes in VBA Excel

Question

The aim is if the checkbox next to the commandbutton "Examine 1" is activated manually the commandbutton "Examine 1" are supposed to be deactivated. Which means that if the overall Button "Execute all" is pushed then all other buttons below will be triggered one by one except the "Examine 1" Button. I have already written the Sub for the "Examine 1" Button and also for the checkBox 1. I just dont know how to get the Sub of the checkbox integrated into the "Examine 1" Sub. Currently, if I push the overall BUtton "Execute all" then the Button "Examine 1" is also executed no matter whether the checkbox is marked or not.

This is the macro of the "Examine 1" Button

Sub Examine_Click()
....
...

This one is Sub for the checkbox which refers to the Sub "Examine_Click()"

Private Sub CheckBox1_Click()

Dim b As Button

Set b = ActiveSheet.Buttons("Button 4")

If CheckBox1.Value = True Then
   b.Enabled = True
   b.Font.ColorIndex = 1
Else: b.Enabled = False
    b.Font.ColorIndex = 15

End If
End Sub

MoondogsMaDawg · Accepted Answer · 2019-12-09 21:51:43Z

1

In your Checkbox code you are never enabling the button: If CheckBox1.Value = True Then: b.Enabled = False | Else: b.Enabled = False. But that's not the problem.

You state that the ExecuteAll button is supposed to call the Subs associated with the other buttons but I'm not sure that the b.Enabled property will prevent the Sub call from running, it will only disable the user's click. In the ExecuteAll_Click() you should test the relevant checkbox before executing the associated button's code.

answered Dec 9, 2019 at 21:51

MoondogsMaDawg

1,71413 silver badges22 bronze badges

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

1 Comment

Crues Over a year ago

you are right. It was a typo. Just corrected it. To your second comment: the "b.Enabled" property doesnt prevent the Sub from running, this is exactly my point. The aim is that the button is deactived and cannot be clicked if the checkmark is set in the checkbox.

Crues · Accepted Answer · 2019-12-10 10:38:52Z

0

the solution is:

 If Worksheets("Control").CheckBox1.Value = True Then

This means that if the checkbox is activated the main macro starts running, otherwise it jumps stright to "end sub"

answered Dec 10, 2019 at 10:38

Crues

1651 gold badge2 silver badges15 bronze badges

Collectives™ on Stack Overflow

How to enable/disable CommandButtons by using checkboxes in VBA Excel

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related