Put cell contents into header

[Guest]

Hello.

This should be an easy one for someone. I want to take the contents of cell
INP.Cells(2,7) and put it into the right header of a worksheet so that what
is in the header changes if the cell contents change.

Here is the code I'm trying to use:

With Sheets("Sheet1").PageSetup
.RightHeader = "&""Arial,Bold""&36" & INP.Cells(2, 7)
End With

It doesn't return an error, but also does not put anything in the header.
Can anyone spot what's wrong with it?

Thanks,

 

[Chip Pearson]

Marty,

You need a space after the 36 in the header string.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Rowan Drummond]

Is INP the sheet name? If so you should set up a change event so that
the header is automatically changed when the value in G2 changes. Right
click the INP sheet tab, select View Code and paste the following event:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 And Target.Address = "$G$2" Then
With Sheets("Sheet1").PageSetup
.RightHeader = "&""Arial,Bold""&36" & Target.Value
End With
End If
End Sub

Hope this helps
Rowan

 

[Dave Peterson]

What's INP?

Is it the name of the worksheet?
..RightHeader = "&""Arial,Bold""&36" & worksheets("INP").Cells(2, 7).value

Is it the code name for a different sheet?

=====

(.value isn't required, but I like it.)

 

[Steve]

What is INP?

Do you mean?

With Sheets("Sheet1").PageSetup
.RightHeader = "&""Arial,Bold""&36" & Cells(2, 7).value
End With

 

[Chip Pearson]

You need a space after the 36 in the header string.

Upon further reflection, you need the space only if the cell
value is numeric.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

That did it. The cell value is numeric, so it worked. Thanks very much.