Is there a way to make a checkbox's Caption dynamic

[MrTinkertrain]

Hello Excelgurus,

I've incorporated a bunch of checkboxes in my workbook.
The captions of those checkboxes are the first names of staff members.
I've added those checkboxes manually and I also manually changed the caption to that particular person's first name.

I was wondering if it's possible to make that somewhat more dynamic.
So for example let's say Hank decides to work somewhere else.
The guy that replaces him is a guy named let's say Joe.

Is there a way to make the caption of that particular checkbox change from "Hank" to "Joe" automatically ?

Any help on this would be highly appreciated.
Also if a let's say "non-checkbox"-solution for this is advisable, I'm open for suggestions

 

[James006]

Hello,

Within your worksheet, are you using ActiveX checkboxes or Forms controls ...?

 

[MrTinkertrain]

Hi James,

I should have mentioned that
I'm using ActiveX checkboxes

 

[My Aswer Is This]

You said you wanted to do this automatically.

Well very little in Excel can be done automatically.

You can run this script and it will do what you want.

Private Sub CommandButton1_Click()
'Modified  1/23/2019  9:39:54 AM  EST
Dim objCkBox As Object
Dim i As Long
Dim OldName As String
Dim NewName As String
OldName = InputBox("Enter Old name")
NewName = InputBox("Enter New Name")
    For i = 1 To Sheets.Count
    
        With Sheets(i)
            For Each objCkBox In .OLEObjects
                
                If TypeName(objCkBox.Object) = "CheckBox" Then
                    If objCkBox.Object.Caption = OldName Then
                        objCkBox.Object.Caption = NewName
                    End If
                End If
 
            Next objCkBox
 
        End With
    Next
End Sub

 

[MrTinkertrain]

Wow, that's a great solution !
Many thanks for this nifty piece of code.
Highly appreciated

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Wow, that's a great solution !
Many thanks for this nifty piece of code.
Highly appreciated