Detect when a checkbox on a worksheet has been checked?

[Hardware Man]

I'm working on a project with checkboxes in columns A and B. If the checkbox for a particular row in column A gets checked, I'd want the contents of column H (for that row) to be italicized. If the checkbox in Column B gets checked, I'd want the contents of column H to be bolded. Unchecking either will obviously uncheck/unbold the contents of Column H.

I think I can handle the creation of a loop that will inspect the entire worksheet and take these actions. What I'm having an issue with is simply detecting whether any checkbox on the workbook has been acted on. This should be easy, but I'm just not seeing it.

 

[Jaafar Tribak]

There are twoo kind of checkboxes - One ActiveX Control and one Form Control . Which one are you using ?

 

[Hardware Man]

I'm recreating the checkboxes each time the sheet activates depending on the data that needs to be inserted into it.

For Z = firstCheckRow To lastCheckRow
ws.CheckBoxes.Add(Cells(Z, firstCheckColumn).Left, Cells(Z, 1).Top, 25, 17.25).Select

With Selection
.Caption = ""
.Value = xlOff
.LinkedCell = "A" & Z
.Display3DShading = False
End With
Next Z

 

[Jaafar Tribak]

From that code, I can see that you are using Form checkboxes.

I would assign a generic macro to all the checkboxes so that when any of the checkboxes is clicked, it will be detected.

Assign the following macro to each shape and you will see how it works for automatically detecting when they are checked\unchecked:

Sub CheckBoxex_Click()
    Dim oChBox As CheckBox
    Set oChBox = ActiveSheet.CheckBoxes(Application.Caller)
    MsgBox IIf(oChBox.Value = xlOn, "You Checked '", "You UnChecked '") & oChBox.Name & "'"
End Sub

 

[Hardware Man]

thanks for your reply but I'm not clear on what you mean.

How do I go about "assigning the macro to each shape"?

 

[Jaafar Tribak]

thanks for your reply but I'm not clear on what you mean.

How do I go about "assigning the macro to each shape"?

Sorry for the confusion. By *shape* I mean *CheckBox*.

Select all the checkboxes while holding down the CTRL key, Righ-Click the mouse and chose Assign Macro from the context menu. This will bring up the Macros dialog. Select the CheckBoxex_Click from the Macros list and click OK.

For the checkboxes that you are adding to the worksheet programmatically, you will need to use the OnAction Property as follows:

For Z = firstCheckRow To lastCheckRow
ws.CheckBoxes.Add(Cells(Z, firstCheckColumn).Left, Cells(Z, 1).Top, 25, 17.25).Select
With Selection
.Caption = ""
.Value = xlOff
.LinkedCell = "A" & Z
.Display3DShading = False
.OnAction = "CheckBoxex_Click"
End With
Next Z

BTW, the CheckBoxex_Click Macro must reside in a Standard Module.

 

[Hardware Man]

Thanks!