Find and rename every second and third checkbox

[natkatten]

I have about 200 checkboxes (Form Controls) on a worksheet. They all have the same description. Let's just say that they are all named "Text1". Now I want every second checkbox to have the description "Text2", while every third has the description "Text3". Text1, Text2, Text3, Text1, Text2, Text3 and so on to the last checkbox.


How do I create a loop that can accomplish this?


Thanks in advance for your help.

 

[lexxie2013]

Hi. You could try the following (adapted from a previous thread):

Sub UpdateChkBoxes()
Dim cb As CheckBox
Dim cbValue As Integer
Dim sht As Worksheet
Set sht = ThisWorkbook.Sheets("Sheet1")
cbValue = 1
With sht
    For Each cb In sht.CheckBoxes
        cb.Caption = "Text " & cbValue
        cbValue = cbValue + 1
        If cbValue > 3 Then cbValue = 1
    Next
End With
End Sub

hth

 

[natkatten]

Thanks. This works really well with my example. BUT - and this is my mistake when I made the example - in my worksheet the real text is not as shown. It is different descriptions. Aka. "DK", "SE", "NO". So I want the checkboxes to show DK, SE, NO, DK, SE, NO and so on.

 

[lexxie2013]

One way would be to put a select case statement in, for example:

Sub UpdateChkBoxes3()
Dim cb As CheckBox
Dim cbValue As Integer
Dim sht As Worksheet
Set sht = ThisWorkbook.Sheets("Sheet1")
cbValue = 1
With sht
    For Each cb In sht.CheckBoxes
        Select Case cbValue
        Case 1
            cb.Caption = "DK"
        Case 2
            cb.Caption = "SE"
        Case 3
            cb.Caption = "NO"
        End Select
        cbValue = cbValue + 1
        If cbValue > 3 Then cbValue = 1
    Next
End With
End Sub

And adapt to your requirements.

 

[natkatten]

Yes. This works like a dream in my sheet. Thank you very much.