Use a variable to identify to control on a user form in VBA

[CharleyDavidson]

I have a user form,SortForm that has 30 radio Buttons on it. The purpose of the form is to allow the user to pick one of the buttons to use as a key to sort the spreadsheet. It is sorting on courses and these change for each registration period.
What I am attempting to do is change the captions of radio Buttons to reflect the course names. I have store the information from the spreadsheet into two arrays, <code>strSortName</code> and <code>intSortCol</code>. <code>strSortName</code> contains the course name. Each radio buttons name starts with "btSort" and then a number 1 to 30.
What I want to do is using the concatenation of "btsort" and the index from my <code>Do</code> loop to update the caption of the button to reflect the course name contain in strSortName




Any Ideas?

Code to date

Sub macSortData()
'
' macSortData Macro
'
' Keyboard Shortcut: Ctrl+Shift+S
'

Dim strVal1 As String, intVal2 As Integer, I As Integer
Dim FormName As String
Dim ControlName As String
Dim strCaptionChange As String
Dim intSortCol(34), strSortName(34) As String, strCap As String


  Sheets("Main").Activate
  
For I = 1 To 34

''strVal1 contains the course name and strVal2 contains the column number for the course

    strVal1 = Cells(4, I + 2).Value
    intVal2 = I + 2
    
2

'tests for ending list of classes
     If Len(strVal1) = 0 Then GoTo sortFinal
     If strVal1 = "# taken" Then GoTo sortFinal
        
    'stores the name and column number in arrays
        strSortName(I) = strVal1
        intSortCol(I) = intVal2
       
'non working caption update code
' concatenated control name
ControlName = "btsort" & Trim(str(I))

strCaptionChange = "SortForm." & ControlName & ".Caption = " & Trim(strSortName(I))

'non=working assignment attempt
Application.Evaluate (strCaptionChange)
Next I
sortFinal:


'Display form
    SortForm.Show


End Sub

 

[RoryA]

Welcome to the forum.

You can’t use Evaluate like that. Try this:

SortForm.controls(ControlName).Caption = Trim(strSortName(I))

 

[CharleyDavidson]

Rory

Thank you, It worked perfectly

Charley

 

[RoryA]

Glad to help.