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

CharleyDavidson

New Member
Joined
Sep 9, 2018
Messages
2
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
Code:
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
 
Last edited by a moderator:

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Welcome to the forum.

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

Code:
SortForm.controls(ControlName).Caption = Trim(strSortName(I))
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top