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:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,390
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Welcome to the forum.

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

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

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,390
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Glad to help. :biggrin:
 

Forum statistics

Threads
1,082,612
Messages
5,366,610
Members
400,906
Latest member
incanus

Some videos you may like

This Week's Hot Topics

Top