Cell Referencing In Header

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kaken6@gmail.com - 21 Mar 2007 16:26 GMT

Hi, I've been trying to use a cell reference in the header of my excel
worksheets. The cell, B11, is in Worksheet1. I have the header
updated when the worksheet is opened. The code looks like:

Private Sub Worksheet_Activate()
Worksheets("Worksheet1").PageSetup.LeftHeader =
Range("Worksheet1'!B11").Value
End Sub

It works well for Worksheet1

But when I try to reference the same cell for my other worksheets with
code looking like:

Private Sub Worksheet_Activate()
Worksheets("Worksheet2").PageSetup.LeftHeader =
Range("Worksheet1'!B11").Value
End Sub

I get a runtime error '1004':
Method 'Range' of object '_Worksheet' failed

Does anyone know how to correct this?
Also, is there some way I can make the cell reference italicized in
the header?
Thank you!

Reply to this Message

Dave Peterson - 21 Mar 2007 16:40 GMT

You're changing the header in the worksheet you just activated?

Private Sub Worksheet_Activate()
me.PageSetup.LeftHeader = me.range("B11").Value
End Sub

Me is the object that owns the code--in this case, the worksheet you just
activated.

If you're actually changing the header in a different worksheet:

Worksheets("Worksheet1").PageSetup.LeftHeader _
= worksheets("worksheet1").range("B11").Value

or to save some typing:

with Worksheets("Worksheet1")
.PageSetup.LeftHeader = .range("B11").Value
end with

Change that worksheet1 to the name you see on the tab in excel.

> Hi, I've been trying to use a cell reference in the header of my excel
> worksheets. The cell, B11, is in Worksheet1. I have the header
[quoted text clipped - 22 lines]
> the header?
> Thank you!

Signature

Dave Peterson

Reply to this Message

kaken6@gmail.com - 21 Mar 2007 17:05 GMT

> You're changing the header in the worksheet you just activated?
>
[quoted text clipped - 52 lines]
>
> - Show quoted text -

Thank you Dave,

Worksheets("Worksheet2").PageSetup.LeftHeader=
worksheets("Worksheet1").range("B11").Value
Works great.
Is there any way to italicize this reference?

Thanks again.

Reply to this Message

Gord Dibben - 21 Mar 2007 19:25 GMT

Sub test()
With Worksheets("Worksheet2").PageSetup
.LeftHeader = "&""Arial,Italic""&12" & _
Worksheets("Worksheet1").Range("B11").Value
End With
End Sub

Gord Dibben MS Excel MVP

>Worksheets("Worksheet2").PageSetup.LeftHeader=
>worksheets("Worksheet1").range("B11").Value
>Works great.
>Is there any way to italicize this reference?
>
>Thanks again.

Reply to this Message