PDA

View Full Version : How to rename TextBoxes on userform



vocedicore
11-30-2018, 01:14 PM
Dear All,

I have created 112 TextBox on userform.
Please advise how to rename these of them by macro.

-TextBox21 - TextBox43 ==> aTextBox21 - aTextBox43
-TextBox44 - TextBox66 ==> bTextBox21 - bTextBox43
-TextBox67 - TextBox89 ==> cTextBox21 - cTextBox43
-TextBox90 - TextBox112 ==> dTextBox21 - dTextBox43

icemail
11-30-2018, 03:47 PM
You can change them name with array easly. Set New names and old names after you can loop them easly.

Dim oldNames As Variant, newNames As Variant, i As Long
oldNames = Array("TextBox21", "TextBox43", "TextBox44")
newNames = Array("aTextBox21", "aTextBox43", "bTextBox21")

Paul_Hossler
11-30-2018, 04:18 PM
You can't rename controls at runtime (i.e. via macro)

It might be possible to automate the process by exporting the UserForm (which gives you a FRM and FRX file), then use a macro to process the FRX binary file replacing strings, and then import the file to the project

The file in binary looks like this

23305

Seems lot a lot of work

Logit
12-01-2018, 12:25 PM
.
Wonder if a redesign is in order for the user form ? 112 textboxes on one form seems like a lot.

Paul_Hossler
12-01-2018, 06:52 PM
.
Wonder if a redesign is in order for the user form ? 112 textboxes on one form seems like a lot.

Especially if they weren't giving meaningful names at design time, e.g. tbStartDate instead of TextBox87

mikerickson
12-02-2018, 06:26 PM
I think this will do the job.


Sub test()
Dim i As Long

With ThisWorkbook.VBProject.VBComponents("Userform1").Designer
For i = 21 To 43
.Controls("TextBox" & i).Name = "a" & "TextBox" & i
.Controls("TextBox" & (i + 23)).Name = "b" & "TextBox" & i
.Controls("TextBox" & (i + 46)).Name = "c" & "TextBox" & i
.Controls("TextBox" & (i + 69)).Name = "d" & "TextBox" & i
Next i
End With
End Sub