Aug 23 2022 01:16 AM - edited Aug 23 2022 01:22 AM
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.
Aug 23 2022 03:31 AM
SolutionTry 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
Aug 25 2022 08:05 AM
Aug 23 2022 03:31 AM
SolutionTry 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