Permanently change userform label captions through vba

[hilyete]

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:

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?

 

[GTO]

By example:

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

In a Standard Module:

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]

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]

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]

Goto File > Options > Trust Center > Trust center settings > Macro Settings > Check trust access to the VBA project object model > Ok > Ok

 

[chousm]

Hi, Fluff:

It works. Thank you so much.

 

[Fluff]

Glad to help & thanks for the feedback