Permanently change userform label captions through vba
Results 1 to 7 of 7

Thread: Permanently change userform label captions through vba
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular hilyete's Avatar
    Join Date
    Aug 2009
    Location
    Nashville, TN
    Posts
    293
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Permanently change userform label captions through vba

    I have a userform that pulls the "captions" for it's 40 labels from cells on sheet 1 (A1 - A40). This is done at the time the userform is initiated. I want to be able to make this a permanent change to the userform so that when it is closed, the labels remain changed with the new captions so I can export the userform to other Office programs with the changes in place. I found a routine that supposedly does that, but I'm using Excel 2010 and it doesn't seem to do anything for me. The code is this:

    Code:
     
    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
    How can I change this so that it works with 2010?

  2. #2
    MrExcel MVP
    Join Date
    Dec 2008
    Location
    Phoenix, Arizona
    Posts
    6,154
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Permanently change userform label captions through vba

    By example:

    Sheet1

     A
    1Jan
    2Feb
    3Mar
    4Apr
    5May
    6Jun
    7Jul
    8Aug
    9Sep
    10Oct
    11Nov
    12Dec


    Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

    In a Standard Module:
    Code:
    Option Explicit
        
    Sub exa1()
    Dim i As Long, n As Long
            
        With ThisWorkbook.VBProject.VBComponents("Userform1").Designer
            For i = 1 To .Controls.Count
                If TypeName(.Controls(i - 1)) = "Label" Then
                    n = n + 1
                    .Controls(i - 1).Caption = Sheet1.Cells(n, 1).Value
                End If
            Next i
        End With
    End Sub
    Please test in a junk copy of your wb of course

  3. #3
    Board Regular hilyete's Avatar
    Join Date
    Aug 2009
    Location
    Nashville, TN
    Posts
    293
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Permanently change userform label captions through vba

    Thanks GTO

    That's what I was looking for. My Userform is turned into a great "Flash Card" game now that can be easily created/ updated in Excel, but then imported into my PowerPoint teaching presentation.

    Tom

  4. #4
    New Member
    Join Date
    May 2017
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Permanently change userform label captions through vba

    I have a similar problem and maybe GTO can help me.

    I have a userform of more than 100 controls on it. Each time when I changed the control properties, the control may shift its position at my clicking on it. It is tedious to reposition the controls over and over. So, I tried to write VBA code to do the job using the following code as an example:

    Private Sub FormLayout()
    Dim VBP As VBProject
    Dim VBC As VBComponent

    Set VBP = ThisWorkbook.VBProject
    Set VBC = VBP.VBComponents("userform1")
    With VBC.Designer
    With .label1
    .Height = 20
    .Width = 120
    .Top = 30
    .Left = 20
    .Font.Name = "Arial Narrow"
    .Font.Size = 10
    .Font.Bold = False
    End With

    With .textbox1
    .Height = 20
    .Width = 120
    .Top = 50
    .Left = 20
    .Font.Name = "Arial Narrow"
    .Font.Size = 10
    .Font.Bold = True
    End With
    End With
    End Sub

    When I ran the code, I got the "Compiler error: User-defined type not defined" for the two dim statements. After I loaded 'Microsoft Visual Basic for Application Extensibility 5.3', those two dim statements were ok.

    Then, I got the "Run-time error 1004: Method 'VBProject' of object '_Workbook' failed" for the statement

    Set VBP = ThisWorkbook.VBProject

    I exited the Excel application and re-started it. I ran the code again, the error message changed to "Run-time error: Programmatic access to Visual Basic Project is not trusted". I could not go any further.

    Please help me solve this problem. Many thanks.

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,384
    Post Thanks / Like
    Mentioned
    440 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Permanently change userform label captions through vba

    Goto File > Options > Trust Center > Trust center settings > Macro Settings > Check trust access to the VBA project object model > Ok > Ok
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    New Member
    Join Date
    May 2017
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Permanently change userform label captions through vba

    Hi, Fluff:

    It works. Thank you so much.

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,384
    Post Thanks / Like
    Mentioned
    440 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Permanently change userform label captions through vba

    Glad to help & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •