Excel VBA code for changing the caption of a checkbox form control

Question

We have been frustrated for hours now in our efforts to write some code that will change the pre-existing captions for Excel checkbox controls already on our worksheet. Here is a snippet of code that we believe is getting us close to the proper object, but we can't seem to generate the actual code necessary to make the change to the caption. We are trying to use the code below to change the captions for checkboxes within the defined range "InputRange" below. Any help would be appreciated as follows:

For Each CurrentShape In ActiveSheet.Shapes
If CurrentShape.Type = msoFormControl Then
With CurrentShape
If .FormControlType = xlCheckBox
If Not Intersect(.TopLeftCell, InputRange ) Is Nothing Then

THIS IS THE POINT WHERE WE NEED HELP MODIFYING CAPTION ...
End If
End If
End With
End If
Next CurrentShape

NOTE: Most articles we see, specify the caption during creation of the checkbox. Unfortunately, our checkboxes already exist and need to be modified after-the-fact. It doesn't appear that the same code strings, used during checkbox creation, will do the job for us here. Obviously, we are missing some important essential.

hansvogelaar · Accepted Answer

LemonPeeler&nbsp;
Try this:
    Dim ChkBox As CheckBox
    For Each ChkBox In ActiveSheet.CheckBoxes
        If Not Intersect(ChkBox.TopLeftCell, InputRange) Is Nothing Then
            ChkBox.Caption = "Your Caption Here"
        End If
    Next ChkBox

Forum Discussion

Excel VBA code for changing the caption of a checkbox form control

2 Replies

Resources