UserForm rename controls

  • I have created a multipage userform and on each page there are going to be 20 sets of four option buttons, each set has a label. There are also other controls on each page


    Currently the labels are all stored on a sheet ie multipage.page1 labels are on sheet1 "A1:A20"


    My question is how do I rename the labels with the contents of the sheet, I have tried the following but other controls get renamed as well

    Code
    1. Private Sub LoadIt()
    2. I = 1
    3. For Each Control In Me.Controls
    4. With Me
    5. Control.Caption = Range("A" & I)
    6. I = I + 1
    7. End With
    8. Next Control
    9. End Sub


    Thanks

  • Hi Wistow


    You need to keep in mind that all Controls are Objects and that Controls like UserForms and Frames can have their own Controls Collection, if Controls have been placed in them. The MultiPage can have Muliti pages and each Page is an Object (Page Object) with the first page being Indexed as 0.


    So, you could use some code like




    Note the use of the Tag Property. You would place the text "NameMe" in the Tag Property of the Labels (at design time) you wanted named that reside on the first Page of the MultiControl. If the Labels were also inside a Frame on the first Page of the MultiPage Object, you would use something like:


    Code
    1. For Each cCntrl In Me.MultiPage1.Pages(0).Frame1.Controls


    Also note the For Each loop Always has to use an Object Variable to loop through the Collection (Controls in this case).

  • Thnks for the reply Dave


    If I understand correctly I have to put somthing into the tag property when I put each label onto the page.


    If this is so, I have a problem that all of my 200+ labels are already in place.


    Is there a labels collection that I can access


    Otherwise I will have to use an If & Left statement with the current names which are all "LabelXX" where XX is a number


    Thanks Graham

  • Hi Graham


    In that case you can use the TypeName to identify the Control Type.



  • Thats what I needed !


    I have just searched the help file again and TypeName is only shown as "to return information about a variable" what else can you use TypeName for?


    Thanks Dave

  • Re: UserForm rename controls


    Yes I know this is an old thread, but, just to prove that I searched :) :)


    I have been trying to use a macro to rename a bunch of controls - textboxes in this case. I have all the logic right to actually get the ones that I want. The problem is that Excel doesn't seem to allow you to rename the control during run time.


    Is this so? If not, then how? If so, is there another way to do it? I have 7x19x2 textboxes I need to rename and would really rather not do it by hand.


    Thanks!!

  • Re: UserForm rename controls


    Hi,


    You can write some code to rename all your controls.
    Create a userfrom and add 3 textboxes.
    Add a standard code module and paste in the following routine.
    [vba]Sub RenameControls()
    '
    ' use tools > references to include a reference to the
    ' extensibility library
    '
    Dim vbpTemp As VBProject
    Dim frmTemp As VBComponent
    Dim cntTemp As MSForms.Control

    Set vbpTemp = Application.VBE.ActiveVBProject
    Set frmTemp = vbpTemp.VBComponents.Item("Userform1")

    For Each cntTemp In frmTemp.Designer.Controls
    If TypeOf cntTemp Is MSForms.TextBox Then
    Select Case UCase(cntTemp.Name)
    Case "TEXTBOX1"
    cntTemp.Name = "txtUsername"
    Case "TEXTBOX2"
    cntTemp.Name = "txtDepartment"
    Case "TEXTBOX3"
    cntTemp.Name = "txtAccount"
    End Select
    End If
    Next

    End Sub[/vba]This will the permanently change the names of the textboxes.
    Obviously you will need to adjust the routine for your controls taking into account whatever system you have of identifying and renaming.

  • Based on Andy's response above I was able to re-purpose his code; goal being to pull old and new Control Names from a spreadsheet and update the Control Names in bulk. Posting in case anyone finds it useful.


    I had old and new names stored in a spreadsheet called "x_Controls": the old Control.Name in Column 2 and the new Control.Name in Column 3 (loop starts on Line 2 as header was in Line 1). Incidentally TypeOf is stored in Column 1 so Andy's "If Statements" could be worked into the logic also if needed.



    Updated a few hundred names with no problems!