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?
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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 :-)
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Goto File > Options > Trust Center > Trust center settings > Macro Settings > Check trust access to the VBA project object model > Ok > Ok
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,265
Members
452,627
Latest member
KitkatToby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top