Announcement

Collapse
No announcement yet.

UserForm rename controls

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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:
    Private Sub LoadIt()
          I = 1
          For Each Control In Me.Controls
                With Me
                Control.Caption = Range("A" & I)
                I = I + 1
                End With
        Next Control
    End Sub
    Thanks
    Last edited by royUK; June 15th, 2005, 19:54.

  • #2
    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


    Code:
    Private Sub UserForm_Initialize()
    Dim cCntrl As Control
    Dim strName As String
    Dim iRow As Integer
    
        For Each cCntrl In Me.MultiPage1.Pages(0).Controls
            If cCntrl.Tag = "NameMe" Then
                iRow = iRow + 1
                strName = Sheet1.Cells(1, 1) 'Sheet CodeName
                cCntrl.Caption = strName
            End If
        Next cCntrl
    End Sub
    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:
    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).

    Comment


    • #3
      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

      Comment


      • #4
        Hi Graham

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


        Code:
        Private Sub UserForm_Initialize()
        Dim cCntrl As Control
        Dim strName As String
        Dim iRow As Integer
         
            For Each cCntrl In Me.MultiPage1.Pages(0).Controls
                If TypeName(cCntrl) = "Label" Then
                    iRow = iRow + 1
                    strName = Sheet1.Cells(1, 1) 'Sheet CodeName
                    cCntrl.Caption = strName
                End If
            Next cCntrl
        End Sub

        Comment


        • #5
          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

          Comment


          • #6
            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!!
            ---
            Old Programmers Never Die ... They just lose their bits

            Comment


            • #7
              Re: UserForm rename controls

              It does not look like you can. Name appears in brackets in the Property Window, probably indicating that you can't change it problematically
              Hope that Helps

              Roy

              New users should read the Forum Rules before posting

              For free Excel tools & articles visit my web site

              RoyUK's Web Site

              royUK's Database Form

              Where to paste code from the Forum

              About me.

              Comment


              • #8
                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.
                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
                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.

                Cheers
                Andy

                Comment


                • #9
                  Re: UserForm rename controls

                  Andy,

                  Thanks mate! That must be the key is having the extents defined.
                  ---
                  Old Programmers Never Die ... They just lose their bits

                  Comment


                  • #10


                    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.

                    Code:
                    Sub RenameControls()
                    Dim vbpTemp As VBProject Dim frmTemp As VBComponent Dim cntTemp As MSForms.Control Set vbpTemp = Application.VBE.ActiveVBProject Set frmTemp = vbpTemp.VBComponents.Item("EditInvForm")
                    Dim source As Worksheet Dim oldName As String, newName As String Dim lRow As Long, i As Long, b As Long, g As Long Set source = ActiveWorkbook.Worksheets("x_Controls") b = 0 g = 0 'using saved function to get last row, might need to change to suit your needs lRow = LastRow(source) For i = 2 To lRow
                    With source
                    oldName = Cells(i, 2) newName = Cells(i, 3)
                    End With On Error Resume Next
                    Set cntTemp = frmTemp.Designer.Controls(oldName) If Err.Num <> 0 Then
                    b = b + 1 'Debug.Print "bad: " & oldName
                    Else
                    cntTemp.Name = newName g = g + 1 'Debug.Print "good: " & oldName & " >>> " & newName
                    End If
                    On Error GoTo 0
                    Next i
                    MsgBox b & " Control Names were Not updated | " & g & " Control Names were Successfully updated"
                    End Sub
                    Updated a few hundred names with no problems!
                    Last edited by MrSteves; April 11th, 2018, 09:11. Reason: Added error-handling

                    Comment

                    Working...
                    X