Change UserForm font

[donbowyer]

Excel 2003
I have a label on a worksheet (which I added using the Control Toolbox
toolbar).
I don't know if it's an ActiveX control or not.
I want to programatically change the label's Font.
The following code doesn't work with the error message:-
Object doesn't support this Property or Method

ActiveSheet.Shapes("Label1").Select
Selection.Verb Verb:=xlOpen
Label1.Caption = "Accounts"
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 13
End With
I presume it's the syntax that's wrong.
Any suggestions would be appreciated.

 

[Chip Pearson]

Try some code like the following:

With Sheet1.Label1.Font
.Name = "Times New Roman"
.Size = 14
.Bold = True
' other properties to change
End With

Note that in the code above, "Sheet1" is the *CodeName* of the worksheet,
which is not necessarily the same as the Name of the sheet that appears in
the tabs in Excel. In VBA, if you CTRL R to open the Project window and
expand the Microsoft Excel Objects "folder" node for your workbook, you'll
see a list of worksheets. The names that are NOT within the parentheses are
the CodeNames (which is how VBA organizes itself). The names that are in
parens are the Sheet Names that appear on the tabs. Use the code name. This
has the advantage that the code will still work even if the sheet's name is
changed, since that doesn't change the code name.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[donbowyer]

Hi Chip
Many thanks. It now works.
--
donwb

Try some code like the following:

With Sheet1.Label1.Font
.Name = "Times New Roman"
.Size = 14
.Bold = True
' other properties to change
End With

Note that in the code above, "Sheet1" is the *CodeName* of the worksheet,
which is not necessarily the same as the Name of the sheet that appears in
the tabs in Excel. In VBA, if you CTRL R to open the Project window and
expand the Microsoft Excel Objects "folder" node for your workbook, you'll
see a list of worksheets. The names that are NOT within the parentheses are
the CodeNames (which is how VBA organizes itself). The names that are in
parens are the Sheet Names that appear on the tabs. Use the code name. This
has the advantage that the code will still work even if the sheet's name is
changed, since that doesn't change the code name.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[donbowyer]

Hi Chip
Just another point, how do I set or change the Caption of the Label?

 

[Rick Rothstein \(MVP - VB\)]

Just another point, how do I set or change the Caption of the Label?

Sheet1.Label1.Caption = "Accounts"

Rick

 

[donbowyer]

Hi Rick
Many thanks - simple isn't it!
--
donwb

Sheet1.Label1.Caption = "Accounts"

Rick