Designer

[Howard31]

Hi all,

Can anyone tell me, how I can change the caption of an EXISTING label in a
userform, in runtime, using the Designer object, so that the caption is saved
even after the userform is unloaded?

 

[Chip Pearson]

Try something like

With ThisWorkbook.VBProject.VBComponents("UserForm1").Designer
.Controls("Label1").Caption = "new text"
End With

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Howard31]

Thanks ever so much Chip, for responding.

I have tried just this, the problem is that I get a runtie error message -
"Object variable or With block variable not set"

This is the code copied and pasted from the VBE:

With ThisWorkbook.VBProject.VBComponents("UFPwd").Designer
.Controls("LblPwd").Caption = "NewPassword"
End With

As I'm already writing to you, can I ask whether it is possible to customize
the Ribbon in Excel 2007 via VBA?

 

[Chip Pearson]

The code looks good to me. You might try breaking down the objects to
see what is actually causing the error. Set a Reference to "Microsoft
Visual Basic For Applications Extensibility Library 5.3" and then use
code like

Sub AAA()
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim Des As msforms.UserForm
Dim Ctrl As msforms.Label

Set VBProj = ThisWorkbook.VBProject
Set VBComp = VBProj.VBComponents("UserForm1")
Set Des = VBComp.Designer
Set Ctrl = Des.Controls("Label1")
Ctrl.Caption = "The new text string"
End Sub

This will break on the line that has the error.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)