Enabling/Disabling a Checkbox Using VBA

[MBCOR]

Hi,

I am trying to enable or disable an ActiveX Controls CheckBox depending on whether a value in an adjacent cell is greater or less than or equal to 0. The CheckBox would be enabled if the value is greater than 0 and disabled if the value is less than or equal to 0.

I found this thread (http://www.mrexcel.com/forum/excel-questions/420586-disable-checkbox-visual-basic-applications.html) and changed the code to one that would read the value from an adjacent cell.

The code I have so far is:

Private Sub CB1_Change()
    Select Case Range("C22").Value > 0
    Case True: CB1.Enabled = True
    Case False: CB1.Enabled = False
    End Select
End Sub

The checkbox disables when I input a value less than or equal to 0 in cell C22 but when I then input a value greater than 0 into cell C22 the cell remains disabled instead of becoming enabled again. The only way for me to enable the cell again is to hit the F8 key multiple times in the VBA.

How can I get the checkbox to change from disabled to enabled based on a cell value after it has been disabled?

Thanks

 

[JoeMo]

Welcome to the Board!

I think you would want to use a change event for the worksheet your checkbox is on rather than a change event for the checkbox itself. Change the checkbox name in the code below to match your checkbox, and be sure you put the code in a module for the worksheet, not a standard module.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$C$22" Then Exit Sub
Me.CheckBox1.Enabled = Target.Value > 0
End Sub

 

[MBCOR]

Thanks JoeMo, this is exactly what I was looking for.

If I wanted to apply this to multiple checkboxes on the same worksheet would I type in this code one on top of the other and change the checkbox name for each?

 

[JoeMo]

Thanks JoeMo, this is exactly what I was looking for.

If I wanted to apply this to multiple checkboxes on the same worksheet would I type in this code one on top of the other and change the checkbox name for each?

Will all checkboxes statuses (enabled/disabled) be governed by the value in the same cell ($C$22)? If yes, then you can just add a single line for each checkbox like this one:

Me.CheckBox1.Enabled = Target.Value > 0

with the appropriate name for each substituted for the name in red font.

If no, then you need to add the other cell conditions as well. In that case, post back with checkbox names, cells and conditions to enable/disable each set if you need help.

 

[MBCOR]

No, the checkboxes statuses will be governed by different cell values.

The conditions needing to be met are all the same. If the cell the checkbox is referencing is greater than 0 then the checkbox will be enable, if not the checkbox will be disabled.

The names of the checkboxes would be CB1-CB115 and the cells they are referencing are $C$22-$C$137. (CB1 references $C$22, CB2 reference $C$23.... and so on until CB115 references $C$137).

 

[JoeMo]

No, the checkboxes statuses will be governed by different cell values.

The conditions needing to be met are all the same. If the cell the checkbox is referencing is greater than 0 then the checkbox will be enable, if not the checkbox will be disabled.

The names of the checkboxes would be CB1-CB115 and the cells they are referencing are $C$22-$C$137. (CB1 references $C$22, CB2 reference $C$23.... and so on until CB115 references $C$137).

When you say "cell the checkbox is referencing", do you mean that CB1 is lined to C22, CB2 to C23, ...,CB115 to C137?

 

[JoeMo]

When you say "cell the checkbox is referencing", do you mean that CB1 is lined to C22, CB2 to C23, ...,CB115 to C137?

Dumb question. Obviously, the answer is "no" since you are independently changing the values in the referenced cell. Code below should do what you want given the "coupling of checkboxes to specific cells" you described. Note: I'm assuming the checkboxes are named CheckBox1, CheckBox2, etc, and have captions CB1, CB2, etc.
If that's not the case, change "CheckBox" to "CB" in the code below.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range, c As Range, cb As OLEObject
Set R = Me.Range("C22:C137")
If Not Intersect(Target, R) Is Nothing Then
    For Each c In Intersect(Target, R)
        For Each cb In Me.OLEObjects
            If cb.Name Like "CheckBox" & Val(Split(c.Address, "$")(2)) - 21 Then
                cb.Enabled = c.Value > 0
            End If
        Next cb
    Next c
End If
End Sub

 

[MBCOR]

Hi Joemo,

This coding did not work for me. Your assumption is correct the checkboxes are named CheckBox1, CheckBox2, etc, and have captions CB1, CB2, etc.

I am trying to link CheckBox1 to Cell C22 directly and if the value in C22 is less than 0 than CheckBox1 would be disabled. Then CheckBox2 would link to Cell C23 and so on until CheckBox115 is linked to Cell C137.

Once I placed the code in it seemed to disable some cells irregardless if their value was greater or less than 0. I posted an image with some of the results below. As you can see the checkbox in Cell D22 is disabled even though it should be enabled as the value in Cell C22 is greater than 0. The checkbox in Cell D27 should be disabled since the value is 0 in Cell C27.

Thanks again for the help.

 

[JoeMo]

Hi Joemo,

This coding did not work for me. Your assumption is correct the checkboxes are named CheckBox1, CheckBox2, etc, and have captions CB1, CB2, etc.

I am trying to link CheckBox1 to Cell C22 directly and if the value in C22 is less than 0 than CheckBox1 would be disabled. Then CheckBox2 would link to Cell C23 and so on until CheckBox115 is linked to Cell C137.

Once I placed the code in it seemed to disable some cells irregardless if their value was greater or less than 0. I posted an image with some of the results below. As you can see the checkbox in Cell D22 is disabled even though it should be enabled as the value in Cell C22 is greater than 0. The checkbox in Cell D27 should be disabled since the value is 0 in Cell C27.

Thanks again for the help.

I don't see any image. I tested the code on two checkboxes using C22 and C23 values and it worked fine for me. If Checkbox1 goes with C22 then Checkbox115 should go with C136 not C137 for the code to work properly because it sets the checkbox number as companion cell number - 21. Are your boxes out of sequence somewhere?

 

[MBCOR]

Yup you were right my checkboxes were out of sequence. Thanks so much for the help!

One last question: If I need to add rows at the bottom do I just increase the range as needed?