Changing UserForm Control Names Based on Column Numbers

AKAUFMAN1 · Mar 27th 2018

Hello! I have a lot of controls on a UserForm that are named in association with the column number they represent. For instance: TextBox1, TextBox2, TextBox3, etc. up to the 50's. Their are not only TextBoxes but also Labels and CheckBoxes that follow the same naming convention. All of my code relies on this naming convention when referencing the controls.
The other day i had a request for a new column A to be added to the sheet, resulting in me having to manually change the name property of every single control to be + 1 of what it currently was set at. My question is, if a column needs to be added anywhere on the sheet in-between current columns, how can i change all of the control names without manually going in and updating the properties (which takes a VERY long time). I tried creating a loop like this, but it doesn't work. The debug error i get is Run-time error 382: Could not set the name property. Can no set property at runtime.

Code

Sub NameChange()
Dim ctrl As MSForms.control


For Each ctrl In UserForm2.MultiPage1.Pages(1).ConsentsFrame2.Controls
    If ctrl.Name Like "Labelx*" Then
        For A = 20 To 43
            Select Case A
                Case 20, 23, 25, 27, 29, 32, 34, 36, 39, 41, 43
                  ctrl.Name = "Labelx" & A
                  A = A + 1
            End Select
            Next
        End If
    Next
    
End Sub

Display More

rabsofty · Mar 27th 2018

attaching your spreadsheet would help.

AKAUFMAN1 · Mar 27th 2018

I have attached the workbook. AllTracking1 is the sheet for which the UserForm2 controls reference the column numbers. . . So ComboBoxx2 represents Column 2 of AllTracking1.
On Page 1 of UserForm2. MultiPage Form their are many more controls which reference the columns such as... Labelx21 , TextBoxx21, CheckBoxx21, CheckBox21, ComboBox21, ComboBoxNo21 all reference column 21 of AllTracking1 sheet. I am just trying to find a fast way to update all of these control names so if i need to add a column after the column which is currently 20 (or T) , i can update all of the controls that originally had 21 in their name to now have 22 in their name.

rabsofty · Mar 27th 2018

from what I see you use the label caption to access the information in detemp.
if you add a column, it still uses the labels name to access the data.
So if I read you right, you want the label# to always reflect the column#?

AKAUFMAN1 · Mar 28th 2018

Yes, but not just the label#..any of the control name #'s to always reflect the column#... This is more straight forward example using the first part of my ListBox1_click event which references just the combobox and textbox controls being filled when the list box is selected:

Code

Private Sub ListBox1_Click() 
'***************************IF LISTBOX HAS BEEN SELECTED*******************************************
    If ListBox1.ListIndex >= 0 Then
        With Me
            SearchButton.Enabled = False
            DeleteRecordButton.Enabled = True
            AddUpdateButton.Enabled = True
            '**********FILL BOXES WITH LIST ITEMS*******************************
            On Error Resume Next
            For listCol = 1 To 46 
                Select Case listCol
                    Case 1, 2, 3, 13, 14
                        Controls("ComboBoxx" & listCol + 1) = ListBox1.Column(listCol)
                    Case 6, 7, 8, 9, 10, 11, 17, 18, 20, 23, 25, 27, 29, 32, 34, 36, 39, 41, 43, 45, 46
                        Controls("TextBoxx" & listCol + 1) = ListBox1.Column(listCol)
                 End Select
            Next listCol
        End With

Display More

ListBox1 is populated using :

Code

LastRow = Tracking.Cells(Rows.Count, "A").End(xlUp).Row   
    Set myTable = Tracking.Range("A6:AU" & LastRow) 
    ListBox1.List = myTable.Value

So the code is filling the boxes based on the ListBox Column Numbers which are associated with the columns on my Tracking.sheet ...therefore if i were to insert a new column anywhere in Tracking sheet other than at the very end, it would throw off all of the control name references.

rabsofty · Mar 28th 2018

I can't think of a way to do it at runtime!

AKAUFMAN1 · Mar 28th 2018

I know...neither can i ! So there is basically no way to modify control names at all unless you manually do it? I wouldn't mind if it was just adjusting the code by increasing the numbers manually but it also requires going into the properties of each control and changing those as well...and as you can see there are about 50 of them on page 1 of the multipage alone.

rabsofty · Mar 28th 2018

I thought of a way (kinda)
You could put the column#'s in the objects tag field (label names would not change or you can give them a name)
eg:
labelx21 tag = 22 (col#)
combobox21 tag = 22
textbox21 = 22

Then when you add a column, you can run a piece of code to change all tags.

**KjBox** · Mar 30th 2018

Rather than using fixed column references, either via the Control Name or Control Tag you could try making it dynamic, by doing something along these lines:

Renaming the combo boxes and text boxes with the table header value that each combo box or text box represents.

For example, in the Demographics page, "Comboxx2" needs to be renamed "Youth Name"

Then in your code, to get the correct column (even if columns have been added or removed) use

Code

Dim lCol as Long, ctl as MsForms.Control


For Each ctl in me.Controls
    If TypeName(ctl) = "ComboBox" Or TypeName(ctl) = "TextBox" Then
        lCol = Application.match(ctl.Name, Tracking.Rows(1), 0)
              ' Your code here to show correct value in the combobox 
    End If
Next

Since all the combo boxes and Text boxes in the Demographics page are disabled, i.e. cannot be changed by the user, why not just use Labels instead of combo boxes and text boxes?

AKAUFMAN1 · Mar 30th 2018

Thank you both for the responses.
KjBox : I hear what you are saying, so if i used that logic of re-naming the controls ( or even putting the column header name in the tag property of each control ) i'm not sure how i would then modify my code that uses the other naming convention i was using by putting column number in the name. For example: part of my listbox click code,

Code

For listCol = 1 To 46 
                Select Case listCol
                    Case 1, 2, 3, 13, 14 
                        Controls("ComboBoxx" & listCol + 1) = ListBox1.Column(listCol)
                    Case 6, 7, 8, 9, 10, 11, 17, 18, 20, 23, 25, 27, 29, 32, 34, 36, 39, 41, 43, 45, 46 
                        Controls("TextBoxx" & listCol + 1) = ListBox1.Column(listCol)
                    Case 4 'STATUS 
                        If ListBox1.Column(listCol) = "Active" Then
                            OptionButton1.Value = True
                        ElseIf ListBox1.Column(listCol) = "Discharged" Then
                            OptionButton2.Value = True
                        End If
                    Case 5 'GENDER' #
                        If ListBox1.Column(listCol) = "Female" Then
                            OptionButton3.Value = True
                        ElseIf ListBox1.Column(listCol) = "Male" Then
                            OptionButton4.Value = True
                        ElseIf ListBox1.Column(listCol) = "Transgender" Then
                            OptionButton5.Value = True
                        End If
End Select
            Next listCol

Display More

So i am having trouble figuring out how i would modify code like this to use the new naming convention?

In regards to your question about the text boxes and combo boxes being disabled. The boxes are enabled until the user clicks the listbox or clicks search. They are then disabled until the user clicks update record button.

AKAUFMAN1 · Mar 30th 2018

Quote from rabsofty

I thought of a way (kinda)
You could put the column#'s in the objects tag field (label names would not change or you can give them a name)
eg:
labelx21 tag = 22 (col#)
combobox21 tag = 22
textbox21 = 22

Then when you add a column, you can run a piece of code to change all tags.

Display More

I cant figure out a way to actually loop through the controls and add or update the tag property at run time either.

**KjBox** · Mar 30th 2018

It is not possible to change a control's Name or Tag at run time.

I will have time over the weekend to look into using the variable column number method further, and get the ListBox1_Click procedure working.

rabsofty · Mar 30th 2018

KjBox, you are right you cannot change a tag at run time. (learned something new)

AKAUFMAN1 · Apr 6th 2018

Quote from KjBox

It is not possible to change a control's Name or Tag at run time.

I will have time over the weekend to look into using the variable column number method further, and get the ListBox1_Click procedure working.

Thank you KjBox! I'm sorry I am just seeing your response. I have been on vacation the past week! If you are able to find a solution, please let me know! It would be incredibly helpful!!

Changing UserForm Control Names Based on Column Numbers

Participate now!

Share