Permanently change userform label captions through vba

hilyete

Active Member
Joined
Aug 19, 2009
Messages
293
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?
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
By example:

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


In a Standard Module:
Rich (BB 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 :)
 

hilyete

Active Member
Joined
Aug 19, 2009
Messages
293
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
 

chousm

New Member
Joined
May 9, 2017
Messages
6
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,786
Office Version
365
Platform
Windows
Goto File > Options > Trust Center > Trust center settings > Macro Settings > Check trust access to the VBA project object model > Ok > Ok
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,786
Office Version
365
Platform
Windows
Glad to help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,102,542
Messages
5,487,469
Members
407,602
Latest member
clang663

This Week's Hot Topics

Top