SOLVED

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

Occasional Contributor

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.

2 Replies
best response confirmed by LemonPeeler (Occasional Contributor)
Solution

@LemonPeeler 

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
Hans, thank you so much, you saved us a ton of wasted time and provided a much smaller set of code! As novices, we often miss the forest for the trees.