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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

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,320
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,320
Office Version
365
Platform
Windows
Glad to help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,101,748
Messages
5,482,625
Members
407,354
Latest member
Calvince

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top