Rename controls in a UserForm 2

[Excelcior]

Does anyone know how to rename a Control other than by using the VB Forms Editor?

I have a form with several hundred controls and I want to rename most of them. The doc correctly tells me that you can't change the .Name property during execution

I don't want to retype them all by hand. Is there any way of doing this?

Thanks in advance for any help

 

[PHV]

But who cares the internal name of controls in a UserForm ???

 

[fumei]

Well PH, I certainly do care. I like my controls to be explicitly named to something meaningful. It makes reading code easier I think.

txtClientName is a much better name that Textbox17, for example.

That being said, I see NO reason for making name changes as part of an execution of code.

Excelcior - could you explain what it is you are doing, and why?

Gerry
My paintings and sculpture

 

[PHV]

txtClientName is a much better name that Textbox17
I obviously agree, but it's a design time issue !

 

[fumei]

Exactly. It is, generally, a design time issue. Once the userform is executing...as long as the internal code is correct and appropriately names the controls....who cares?

Which is why I am questioning the OP. The only thing I can think of is they copied the userform from one project to another in an attempt to re-use it. ??????

In any case, the answer really is...NO. You can not set control names at run-time. Even using another code module to try it will not work. The Name property of existing controls can not be set by executing code. The Name CAN be set for controls that are added at run-time.

Gerry
My paintings and sculpture

 

[Excelcior]

Thanks for your input. I *do* realize that the doc says you *cannot* change the .Name property under execution.

Here's the scenario: Company B buys company A and inherits all its software including several Excel based VBA applications with lots of forms and bokoo controls. Ah, but Company B has very strict programming rules (company A was a Cowboy outfit).

So poor programmer C (that's me) has to change "GoFotItBtn" to "GoForItButton" because "All CommandButtons must be named 'xxxButton'". So, the answer to PHV is "The pointy-headed management at company B cares ...".

I was hoping that someone else had been confronted by this problem and wrote (or knows about) a tool that massages a .frx file allowing me to change the @#$^$*! control names. Otherwise its *days* with the Visual Basic Editor.

Anyone???

 

[fumei]

Ah, I understand now. You have my sympathies.

Sorry...it is days in the VBE for you. However, if someone DOES come up with something, or you find one, please post here! Some of us would be very interested in a process that, AFAIK, can not be done.

Although.....hmmmmm......I wonder if.....hmmmmmm.....

Gerry
My paintings and sculpture

 

[fumei]

Darn....thought I had an idea...but what are the odds of THAT?

Nope. I think you are stuck. You have no access to existing control properties. Heck you can't even remove an existing control at run-time.

Gerry
My paintings and sculpture

 

[combo]

You can change controls names by code, a reference to VBIDE (5.3) is required and programmatic access to vbproject.
However, with random names, you will never be sure that you found the right control and corresponding proper code. Depending on project structure, you may also need to search other modules, and maybe worksheets with embedded activex controls.
You can also have names like cmdButton and cmdBtn controls on the same form, automatic renaming will cause name conflict.
The number of workbooks to check is another topic, as you will need to open them one by one, review, and save.
So the best would be an interactive application; a simple code to rename commandbuttons within the same project could be:

Dim vbcToCheck As VBIDE.VBComponent
Dim cControl As Control
Dim sOldName As String, sNewName As String
Dim sOldCode As String, sNewCode As String
For Each vbcToCheck In ThisWorkbook.VBProject.VBComponents
If Not vbcToCheck.Designer Is Nothing Then
    For Each cControl In vbcToCheck.Designer.Controls
        If cControl.Name Like "*Btn" And TypeName(cControl) = "MSForms.CommandButton.1" Then
            sOldName = cControl.Name
            sNewName = Replace(sOldName, "Btn", "Button")
            cControl.Name = sNewName
            With vbcToCheck.CodeModule
                sOldCode = .Lines(1, .CountOfLines)
                sNewCode = Replace(sOldCode, sOldName, sNewName)
                .DeleteLines 1, .CountOfLines
                .InsertLines 1, sNewCode
            End With
        End If
    Next cControl
End If
Next vbcToCheck

combo

 

[TonyJollans]

Some of the procss can be automated but do remember that there can be references to controls anywhere within the project so it is not sufficient just to scan the userform's code module.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[Excelcior]

Again, thanks for the input. I wrote a quick and dirty syntaxer (token analyzer) so I am not worried that I have e.g. fooBtn and fooButton in the same module. I know which controls I have to rename and what the names have to be.

However, Combo, can you tell me what VBIDE is and how I access it? I tried your code, but my Excel VB Editor says Undefined User-Defined type when I try to compile the snippet from your post (just like it did when I tried Dim vbc as VBComponent).

Also, can anyone tell me why the one instance I have of Excel 2003 won't let me check the "Trust the VB Project" under Tools>Macros>Security??????? My Excel from Office 2000 lets me check that but it is greyed out in Excel 2003.

Have a nice day, all

 

[PHV]

Undefined User-Defined type
When in VBE menu Tools -> References ...
Tick the Microsoft Visual Basic for Application Extensibility library.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TonyJollans]

I don't know why your option is greyed out in 2003 - a policy perhaps, but it is not an option in 2000 so I'm not quite sure what you're doing.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[Excelcior]

Well, well well well

They said it couldn't be done, but between combo and PHV I was (am!) able to rename my controls via program!!! For me the problem is solved.

Thanks 1000000 Tek-Tips (I gave a * to both PHV and combo).

For Tony Jollens, when I want to import my User Form into a running VBA project (to change the control names and then export it to disk) I have to check the "Trust the VBA project" checkbox under Tools > Macros > Security. This *is* an option using the Excel from Office 2000. However, when I try to do it using the Excel from Office 2003, the checkbox is greyed out and I cannot check it (so I can't even load the Form!!) I do not know why this is (or should be) the case

In any case, checking the Microsoft Visual Basic for Application Extensibility library (as PHV suggested) lets me do what I want. Thanks again.

Excelcior

 

[TonyJollans]

We must be talking about different things. If you've got your code working then we're all happy.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association