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.
--
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" <(E-Mail Removed)> wrote in message
news:46439371-D7CE-44C3-A2DA-(E-Mail Removed)...
> 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.
> --
> donwb

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


"Chip Pearson" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:46439371-D7CE-44C3-A2DA-(E-Mail Removed)...
> > 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.
> > --
> > donwb
>

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


"Chip Pearson" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:46439371-D7CE-44C3-A2DA-(E-Mail Removed)...
> > 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.
> > --
> > donwb
>

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

Sheet1.Label1.Caption = "Accounts"

Rick

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


"Rick Rothstein (MVP - VB)" wrote:

> > Just another point, how do I set or change the Caption of the Label?
>
> Sheet1.Label1.Caption = "Accounts"
>
> Rick
>