Need to format text in header, but value is generated using VBA

This is a multi-part message in MIME format.

------=_NextPart_000_0006_01C6360C.2372C770
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hello,

I wanted to reference a cell value in my spreadsheet header and found =
out how to do that using this function:

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Dim WS As Worksheet
For Each WS In Worksheets
    ActiveSheet.PageSetup.RightHeader =3D _
    Format(Worksheets("Time Period Info").Range("B3").Value)
       =20
Next WS
End Sub


However, this leaves my text for this portion of the header at Arial =
Size 10 font Regular.  I would like it Arial Size 20 Bold.  How can I =
set it to this format of text?

Thanks for the help!

--Stacey
------=_NextPart_000_0006_01C6360C.2372C770
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2900.2802" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY>
<DIV><FONT face=3DArial size=3D2>Hello,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I wanted to reference a cell value in =
my=20
spreadsheet header and found out how to do that using this=20
function:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Private Sub Workbook_BeforePrint(Cancel =
As=20
Boolean)</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Dim WS As Worksheet<BR>For Each WS In=20
Worksheets<BR>&nbsp;&nbsp;&nbsp; ActiveSheet.PageSetup.RightHeader =3D=20
_<BR>&nbsp;&nbsp;&nbsp; Format(Worksheets("Time Period=20
Info").Range("B3").Value)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
<BR>Next=20
WS<BR>End Sub</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>However, this leaves my text for this =
portion of=20
the header at Arial Size 10 font Regular.&nbsp; I would like it Arial =
Size 20=20
Bold.&nbsp; How can I set it to this format of text?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Thanks for the help!</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>--Stacey</FONT></DIV></BODY></HTML>

------=_NextPart_000_0006_01C6360C.2372C770--

0
happyjunk (12)
2/20/2006 6:55:18 PM
excel.charting 18370 articles. 0 followers. Follow

11 Replies
534 Views

Similar Articles

[PageSpeed] 20

Hi,

You can add formatting codes to change font size and bold.

ActiveSheet.PageSetup.RightHeader = _
  "&20&B" & Format(Worksheets("Time Period Info").Range("B3").Value)

Where &B Turns bold printing on or off and &nn Prints the characters 
that follow in the specified font size. Use a two-digit number to 
specify a size in points.

Use the Help and serach for 'Formatting Codes for Headers and Footers' 
for a comprehensive list.

Cheers
Andy

Stacey wrote:
> Hello,
>  
> I wanted to reference a cell value in my spreadsheet header and found 
> out how to do that using this function:
>  
> Private Sub Workbook_BeforePrint(Cancel As Boolean)
>  
> Dim WS As Worksheet
> For Each WS In Worksheets
>     ActiveSheet.PageSetup.RightHeader = _
>     Format(Worksheets("Time Period Info").Range("B3").Value)
>        
> Next WS
> End Sub
>  
>  
> However, this leaves my text for this portion of the header at Arial 
> Size 10 font Regular.  I would like it Arial Size 20 Bold.  How can I 
> set it to this format of text?
>  
> Thanks for the help!
>  
> --Stacey

-- 

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
0
andy9699 (3616)
2/21/2006 10:17:02 AM
Exactly what I needed.  Thanks again!
"Andy Pope" <andy@andypope.info> wrote in message 
news:%23MVO3$sNGHA.3988@TK2MSFTNGP09.phx.gbl...
> Hi,
>
> You can add formatting codes to change font size and bold.
>
> ActiveSheet.PageSetup.RightHeader = _
>  "&20&B" & Format(Worksheets("Time Period Info").Range("B3").Value)
>
> Where &B Turns bold printing on or off and &nn Prints the characters that 
> follow in the specified font size. Use a two-digit number to specify a 
> size in points.
>
> Use the Help and serach for 'Formatting Codes for Headers and Footers' for 
> a comprehensive list.
>
> Cheers
> Andy
>
> Stacey wrote:
>> Hello,
>>  I wanted to reference a cell value in my spreadsheet header and found 
>> out how to do that using this function:
>>  Private Sub Workbook_BeforePrint(Cancel As Boolean)
>>  Dim WS As Worksheet
>> For Each WS In Worksheets
>>     ActiveSheet.PageSetup.RightHeader = _
>>     Format(Worksheets("Time Period Info").Range("B3").Value)
>>        Next WS
>> End Sub
>>  However, this leaves my text for this portion of the header at Arial 
>> Size 10 font Regular.  I would like it Arial Size 20 Bold.  How can I set 
>> it to this format of text?
>>  Thanks for the help!
>>  --Stacey
>
> -- 
>
> Andy Pope, Microsoft MVP - Excel
> http://www.andypope.info 


0
happyjunk (12)
2/21/2006 4:56:04 PM
One more quick question:

I want to keep the same type of text formatting, but I need it slightly 
revised so that it actually references two cells and displays them with a 
dash in the middle such as:
Q1-Q2

Is this possible?  Currently I have it set to display just 1 cell looking 
like such:
Q1

Thanks for the help!

I also need to to display the info for two different cells.
"Andy Pope" <andy@andypope.info> wrote in message 
news:%23MVO3$sNGHA.3988@TK2MSFTNGP09.phx.gbl...
> Hi,
>
> You can add formatting codes to change font size and bold.
>
> ActiveSheet.PageSetup.RightHeader = _
>  "&20&B" & Format(Worksheets("Time Period Info").Range("B3").Value)
>
> Where &B Turns bold printing on or off and &nn Prints the characters that 
> follow in the specified font size. Use a two-digit number to specify a 
> size in points.
>
> Use the Help and serach for 'Formatting Codes for Headers and Footers' for 
> a comprehensive list.
>
> Cheers
> Andy
>
> Stacey wrote:
>> Hello,
>>  I wanted to reference a cell value in my spreadsheet header and found 
>> out how to do that using this function:
>>  Private Sub Workbook_BeforePrint(Cancel As Boolean)
>>  Dim WS As Worksheet
>> For Each WS In Worksheets
>>     ActiveSheet.PageSetup.RightHeader = _
>>     Format(Worksheets("Time Period Info").Range("B3").Value)
>>        Next WS
>> End Sub
>>  However, this leaves my text for this portion of the header at Arial 
>> Size 10 font Regular.  I would like it Arial Size 20 Bold.  How can I set 
>> it to this format of text?
>>  Thanks for the help!
>>  --Stacey
>
> -- 
>
> Andy Pope, Microsoft MVP - Excel
> http://www.andypope.info 


0
happyjunk (12)
2/21/2006 5:57:10 PM
I found a work around.  I created a new cell, combined the other two with a 
dash in the middle, and then referenced that cell instead.  Thanks!
"Andy Pope" <andy@andypope.info> wrote in message 
news:%23MVO3$sNGHA.3988@TK2MSFTNGP09.phx.gbl...
> Hi,
>
> You can add formatting codes to change font size and bold.
>
> ActiveSheet.PageSetup.RightHeader = _
>  "&20&B" & Format(Worksheets("Time Period Info").Range("B3").Value)
>
> Where &B Turns bold printing on or off and &nn Prints the characters that 
> follow in the specified font size. Use a two-digit number to specify a 
> size in points.
>
> Use the Help and serach for 'Formatting Codes for Headers and Footers' for 
> a comprehensive list.
>
> Cheers
> Andy
>
> Stacey wrote:
>> Hello,
>>  I wanted to reference a cell value in my spreadsheet header and found 
>> out how to do that using this function:
>>  Private Sub Workbook_BeforePrint(Cancel As Boolean)
>>  Dim WS As Worksheet
>> For Each WS In Worksheets
>>     ActiveSheet.PageSetup.RightHeader = _
>>     Format(Worksheets("Time Period Info").Range("B3").Value)
>>        Next WS
>> End Sub
>>  However, this leaves my text for this portion of the header at Arial 
>> Size 10 font Regular.  I would like it Arial Size 20 Bold.  How can I set 
>> it to this format of text?
>>  Thanks for the help!
>>  --Stacey
>
> -- 
>
> Andy Pope, Microsoft MVP - Excel
> http://www.andypope.info 


0
happyjunk (12)
2/21/2006 6:11:43 PM
Okay, last time, I promise. I often will select 4 different worksheets and 
have them all print at once, and I thought this VBA macro would update all 
of them (because they are all active sheets) at the same time.  But for some 
reason its JUST updating the very first one with the header info.  Do you 
know why and how I could fix it?

Thanks again for the help!
"Andy Pope" <andy@andypope.info> wrote in message 
news:%23MVO3$sNGHA.3988@TK2MSFTNGP09.phx.gbl...
> Hi,
>
> You can add formatting codes to change font size and bold.
>
> ActiveSheet.PageSetup.RightHeader = _
>  "&20&B" & Format(Worksheets("Time Period Info").Range("B3").Value)
>
> Where &B Turns bold printing on or off and &nn Prints the characters that 
> follow in the specified font size. Use a two-digit number to specify a 
> size in points.
>
> Use the Help and serach for 'Formatting Codes for Headers and Footers' for 
> a comprehensive list.
>
> Cheers
> Andy
>
> Stacey wrote:
>> Hello,
>>  I wanted to reference a cell value in my spreadsheet header and found 
>> out how to do that using this function:
>>  Private Sub Workbook_BeforePrint(Cancel As Boolean)
>>  Dim WS As Worksheet
>> For Each WS In Worksheets
>>     ActiveSheet.PageSetup.RightHeader = _
>>     Format(Worksheets("Time Period Info").Range("B3").Value)
>>        Next WS
>> End Sub
>>  However, this leaves my text for this portion of the header at Arial 
>> Size 10 font Regular.  I would like it Arial Size 20 Bold.  How can I set 
>> it to this format of text?
>>  Thanks for the help!
>>  --Stacey
>
> -- 
>
> Andy Pope, Microsoft MVP - Excel
> http://www.andypope.info 


0
happyjunk (12)
2/21/2006 11:29:41 PM
Hi Stacey,

Good to see you worked out the combined cell problem.

Only one sheet is active even if you have multiple sheets selected.

This revision to your code should process all sheets.

Sub X()
     Dim WS As Worksheet

     For Each WS In Worksheets
         WS.PageSetup.RightHeader = _
             "&20&B" & _
Format(Worksheets("Time Period Info").Range("B3").Value)
     Next WS
End Sub

Cheers
Andy

Stacey wrote:
> Okay, last time, I promise. I often will select 4 different worksheets and 
> have them all print at once, and I thought this VBA macro would update all 
> of them (because they are all active sheets) at the same time.  But for some 
> reason its JUST updating the very first one with the header info.  Do you 
> know why and how I could fix it?
> 
> Thanks again for the help!
> "Andy Pope" <andy@andypope.info> wrote in message 
> news:%23MVO3$sNGHA.3988@TK2MSFTNGP09.phx.gbl...
> 
>>Hi,
>>
>>You can add formatting codes to change font size and bold.
>>
>>ActiveSheet.PageSetup.RightHeader = _
>> "&20&B" & Format(Worksheets("Time Period Info").Range("B3").Value)
>>
>>Where &B Turns bold printing on or off and &nn Prints the characters that 
>>follow in the specified font size. Use a two-digit number to specify a 
>>size in points.
>>
>>Use the Help and serach for 'Formatting Codes for Headers and Footers' for 
>>a comprehensive list.
>>
>>Cheers
>>Andy
>>
>>Stacey wrote:
>>
>>>Hello,
>>> I wanted to reference a cell value in my spreadsheet header and found 
>>>out how to do that using this function:
>>> Private Sub Workbook_BeforePrint(Cancel As Boolean)
>>> Dim WS As Worksheet
>>>For Each WS In Worksheets
>>>    ActiveSheet.PageSetup.RightHeader = _
>>>    Format(Worksheets("Time Period Info").Range("B3").Value)
>>>       Next WS
>>>End Sub
>>> However, this leaves my text for this portion of the header at Arial 
>>>Size 10 font Regular.  I would like it Arial Size 20 Bold.  How can I set 
>>>it to this format of text?
>>> Thanks for the help!
>>> --Stacey
>>
>>-- 
>>
>>Andy Pope, Microsoft MVP - Excel
>>http://www.andypope.info 
> 
> 
> 

-- 

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
0
andy9699 (3616)
2/22/2006 8:54:22 AM
Thanks.
I'm not sure I'm entering the information quite right.  I left the portion 
at the top that read "BeforePrint" but it wouldn't work when I left it 
there.  However, when I deleted it, the macro will no longer update.  What 
do you suggest?
"Andy Pope" <andy@andypope.info> wrote in message 
news:On7nV24NGHA.532@TK2MSFTNGP15.phx.gbl...
> Hi Stacey,
>
> Good to see you worked out the combined cell problem.
>
> Only one sheet is active even if you have multiple sheets selected.
>
> This revision to your code should process all sheets.
>
> Sub X()
>     Dim WS As Worksheet
>
>     For Each WS In Worksheets
>         WS.PageSetup.RightHeader = _
>             "&20&B" & _
> Format(Worksheets("Time Period Info").Range("B3").Value)
>     Next WS
> End Sub
>
> Cheers
> Andy
>
> Stacey wrote:
>> Okay, last time, I promise. I often will select 4 different worksheets 
>> and have them all print at once, and I thought this VBA macro would 
>> update all of them (because they are all active sheets) at the same time. 
>> But for some reason its JUST updating the very first one with the header 
>> info.  Do you know why and how I could fix it?
>>
>> Thanks again for the help!
>> "Andy Pope" <andy@andypope.info> wrote in message 
>> news:%23MVO3$sNGHA.3988@TK2MSFTNGP09.phx.gbl...
>>
>>>Hi,
>>>
>>>You can add formatting codes to change font size and bold.
>>>
>>>ActiveSheet.PageSetup.RightHeader = _
>>> "&20&B" & Format(Worksheets("Time Period Info").Range("B3").Value)
>>>
>>>Where &B Turns bold printing on or off and &nn Prints the characters that 
>>>follow in the specified font size. Use a two-digit number to specify a 
>>>size in points.
>>>
>>>Use the Help and serach for 'Formatting Codes for Headers and Footers' 
>>>for a comprehensive list.
>>>
>>>Cheers
>>>Andy
>>>
>>>Stacey wrote:
>>>
>>>>Hello,
>>>> I wanted to reference a cell value in my spreadsheet header and found 
>>>> out how to do that using this function:
>>>> Private Sub Workbook_BeforePrint(Cancel As Boolean)
>>>> Dim WS As Worksheet
>>>>For Each WS In Worksheets
>>>>    ActiveSheet.PageSetup.RightHeader = _
>>>>    Format(Worksheets("Time Period Info").Range("B3").Value)
>>>>       Next WS
>>>>End Sub
>>>> However, this leaves my text for this portion of the header at Arial 
>>>> Size 10 font Regular.  I would like it Arial Size 20 Bold.  How can I 
>>>> set it to this format of text?
>>>> Thanks for the help!
>>>> --Stacey
>>>
>>>-- 
>>>
>>>Andy Pope, Microsoft MVP - Excel
>>>http://www.andypope.info
>>
>>
>>
>
> -- 
>
> Andy Pope, Microsoft MVP - Excel
> http://www.andypope.info 


0
happyjunk (12)
2/22/2006 4:27:37 PM
Okay, so another quick revision.  I found that I leave the portion that 
reads "Before Print" and eliminate the part of your code that reads Sub X().

Now the problem I have is that when I select 4 of the worksheets and then 
hit Print or Print preview, it deselects the last 3 worksheets and only 
updates and prints the 1st one.  Then after using the print utilitiy the 3 
additional worksheets are no longer highlighted and active.  I'm not sure 
wha'ts causing this, but I need to be able to update and print all of the 
selected worksheets at once.
"Andy Pope" <andy@andypope.info> wrote in message 
news:On7nV24NGHA.532@TK2MSFTNGP15.phx.gbl...
> Hi Stacey,
>
> Good to see you worked out the combined cell problem.
>
> Only one sheet is active even if you have multiple sheets selected.
>
> This revision to your code should process all sheets.
>
> Sub X()
>     Dim WS As Worksheet
>
>     For Each WS In Worksheets
>         WS.PageSetup.RightHeader = _
>             "&20&B" & _
> Format(Worksheets("Time Period Info").Range("B3").Value)
>     Next WS
> End Sub
>
> Cheers
> Andy
>
> Stacey wrote:
>> Okay, last time, I promise. I often will select 4 different worksheets 
>> and have them all print at once, and I thought this VBA macro would 
>> update all of them (because they are all active sheets) at the same time. 
>> But for some reason its JUST updating the very first one with the header 
>> info.  Do you know why and how I could fix it?
>>
>> Thanks again for the help!
>> "Andy Pope" <andy@andypope.info> wrote in message 
>> news:%23MVO3$sNGHA.3988@TK2MSFTNGP09.phx.gbl...
>>
>>>Hi,
>>>
>>>You can add formatting codes to change font size and bold.
>>>
>>>ActiveSheet.PageSetup.RightHeader = _
>>> "&20&B" & Format(Worksheets("Time Period Info").Range("B3").Value)
>>>
>>>Where &B Turns bold printing on or off and &nn Prints the characters that 
>>>follow in the specified font size. Use a two-digit number to specify a 
>>>size in points.
>>>
>>>Use the Help and serach for 'Formatting Codes for Headers and Footers' 
>>>for a comprehensive list.
>>>
>>>Cheers
>>>Andy
>>>
>>>Stacey wrote:
>>>
>>>>Hello,
>>>> I wanted to reference a cell value in my spreadsheet header and found 
>>>> out how to do that using this function:
>>>> Private Sub Workbook_BeforePrint(Cancel As Boolean)
>>>> Dim WS As Worksheet
>>>>For Each WS In Worksheets
>>>>    ActiveSheet.PageSetup.RightHeader = _
>>>>    Format(Worksheets("Time Period Info").Range("B3").Value)
>>>>       Next WS
>>>>End Sub
>>>> However, this leaves my text for this portion of the header at Arial 
>>>> Size 10 font Regular.  I would like it Arial Size 20 Bold.  How can I 
>>>> set it to this format of text?
>>>> Thanks for the help!
>>>> --Stacey
>>>
>>>-- 
>>>
>>>Andy Pope, Microsoft MVP - Excel
>>>http://www.andypope.info
>>
>>
>>
>
> -- 
>
> Andy Pope, Microsoft MVP - Excel
> http://www.andypope.info 


0
happyjunk (12)
2/22/2006 4:35:14 PM
This should do it just prior to printing.

Private Sub Workbook_BeforePrint(Cancel As Boolean)

     Dim WS As Worksheet
     For Each WS In Worksheets
         WS.PageSetup.RightHeader = "&20&B" & _
             Format(Worksheets("Time Period Info").Range("B3").Value)

     Next WS
End Sub

Cheers
Andy

Stacey wrote:
> Thanks.
> I'm not sure I'm entering the information quite right.  I left the portion 
> at the top that read "BeforePrint" but it wouldn't work when I left it 
> there.  However, when I deleted it, the macro will no longer update.  What 
> do you suggest?
> "Andy Pope" <andy@andypope.info> wrote in message 
> news:On7nV24NGHA.532@TK2MSFTNGP15.phx.gbl...
> 
>>Hi Stacey,
>>
>>Good to see you worked out the combined cell problem.
>>
>>Only one sheet is active even if you have multiple sheets selected.
>>
>>This revision to your code should process all sheets.
>>
>>Sub X()
>>    Dim WS As Worksheet
>>
>>    For Each WS In Worksheets
>>        WS.PageSetup.RightHeader = _
>>            "&20&B" & _
>>Format(Worksheets("Time Period Info").Range("B3").Value)
>>    Next WS
>>End Sub
>>
>>Cheers
>>Andy
>>
>>Stacey wrote:
>>
>>>Okay, last time, I promise. I often will select 4 different worksheets 
>>>and have them all print at once, and I thought this VBA macro would 
>>>update all of them (because they are all active sheets) at the same time. 
>>>But for some reason its JUST updating the very first one with the header 
>>>info.  Do you know why and how I could fix it?
>>>
>>>Thanks again for the help!
>>>"Andy Pope" <andy@andypope.info> wrote in message 
>>>news:%23MVO3$sNGHA.3988@TK2MSFTNGP09.phx.gbl...
>>>
>>>
>>>>Hi,
>>>>
>>>>You can add formatting codes to change font size and bold.
>>>>
>>>>ActiveSheet.PageSetup.RightHeader = _
>>>>"&20&B" & Format(Worksheets("Time Period Info").Range("B3").Value)
>>>>
>>>>Where &B Turns bold printing on or off and &nn Prints the characters that 
>>>>follow in the specified font size. Use a two-digit number to specify a 
>>>>size in points.
>>>>
>>>>Use the Help and serach for 'Formatting Codes for Headers and Footers' 
>>>>for a comprehensive list.
>>>>
>>>>Cheers
>>>>Andy
>>>>
>>>>Stacey wrote:
>>>>
>>>>
>>>>>Hello,
>>>>>I wanted to reference a cell value in my spreadsheet header and found 
>>>>>out how to do that using this function:
>>>>>Private Sub Workbook_BeforePrint(Cancel As Boolean)
>>>>>Dim WS As Worksheet
>>>>>For Each WS In Worksheets
>>>>>   ActiveSheet.PageSetup.RightHeader = _
>>>>>   Format(Worksheets("Time Period Info").Range("B3").Value)
>>>>>      Next WS
>>>>>End Sub
>>>>>However, this leaves my text for this portion of the header at Arial 
>>>>>Size 10 font Regular.  I would like it Arial Size 20 Bold.  How can I 
>>>>>set it to this format of text?
>>>>>Thanks for the help!
>>>>>--Stacey
>>>>
>>>>-- 
>>>>
>>>>Andy Pope, Microsoft MVP - Excel
>>>>http://www.andypope.info
>>>
>>>
>>>
>>-- 
>>
>>Andy Pope, Microsoft MVP - Excel
>>http://www.andypope.info 
> 
> 
> 

-- 

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
0
andy9699 (3616)
2/22/2006 4:37:27 PM
Thanks.  That did make it update prior to printing, but now I appear to have 
another problem.

Now the problem I have is that when I select 4 of the worksheets and then
hit Print or Print preview, it deselects the last 3 worksheets and only
updates and prints the 1st one.  Then after using the print utilitiy the 3
additional worksheets are no longer highlighted and active.  I'm not sure
what's causing this, but I need to be able to update and print all of the
selected worksheets at once.

"Andy Pope" <andy@andypope.info> wrote in message 
news:eP2KG58NGHA.3272@tk2msftngp13.phx.gbl...
> This should do it just prior to printing.
>
> Private Sub Workbook_BeforePrint(Cancel As Boolean)
>
>     Dim WS As Worksheet
>     For Each WS In Worksheets
>         WS.PageSetup.RightHeader = "&20&B" & _
>             Format(Worksheets("Time Period Info").Range("B3").Value)
>
>     Next WS
> End Sub
>
> Cheers
> Andy
>
> Stacey wrote:
>> Thanks.
>> I'm not sure I'm entering the information quite right.  I left the 
>> portion at the top that read "BeforePrint" but it wouldn't work when I 
>> left it there.  However, when I deleted it, the macro will no longer 
>> update.  What do you suggest?
>> "Andy Pope" <andy@andypope.info> wrote in message 
>> news:On7nV24NGHA.532@TK2MSFTNGP15.phx.gbl...
>>
>>>Hi Stacey,
>>>
>>>Good to see you worked out the combined cell problem.
>>>
>>>Only one sheet is active even if you have multiple sheets selected.
>>>
>>>This revision to your code should process all sheets.
>>>
>>>Sub X()
>>>    Dim WS As Worksheet
>>>
>>>    For Each WS In Worksheets
>>>        WS.PageSetup.RightHeader = _
>>>            "&20&B" & _
>>>Format(Worksheets("Time Period Info").Range("B3").Value)
>>>    Next WS
>>>End Sub
>>>
>>>Cheers
>>>Andy
>>>
>>>Stacey wrote:
>>>
>>>>Okay, last time, I promise. I often will select 4 different worksheets 
>>>>and have them all print at once, and I thought this VBA macro would 
>>>>update all of them (because they are all active sheets) at the same 
>>>>time. But for some reason its JUST updating the very first one with the 
>>>>header info.  Do you know why and how I could fix it?
>>>>
>>>>Thanks again for the help!
>>>>"Andy Pope" <andy@andypope.info> wrote in message 
>>>>news:%23MVO3$sNGHA.3988@TK2MSFTNGP09.phx.gbl...
>>>>
>>>>
>>>>>Hi,
>>>>>
>>>>>You can add formatting codes to change font size and bold.
>>>>>
>>>>>ActiveSheet.PageSetup.RightHeader = _
>>>>>"&20&B" & Format(Worksheets("Time Period Info").Range("B3").Value)
>>>>>
>>>>>Where &B Turns bold printing on or off and &nn Prints the characters 
>>>>>that follow in the specified font size. Use a two-digit number to 
>>>>>specify a size in points.
>>>>>
>>>>>Use the Help and serach for 'Formatting Codes for Headers and Footers' 
>>>>>for a comprehensive list.
>>>>>
>>>>>Cheers
>>>>>Andy
>>>>>
>>>>>Stacey wrote:
>>>>>
>>>>>
>>>>>>Hello,
>>>>>>I wanted to reference a cell value in my spreadsheet header and found 
>>>>>>out how to do that using this function:
>>>>>>Private Sub Workbook_BeforePrint(Cancel As Boolean)
>>>>>>Dim WS As Worksheet
>>>>>>For Each WS In Worksheets
>>>>>>   ActiveSheet.PageSetup.RightHeader = _
>>>>>>   Format(Worksheets("Time Period Info").Range("B3").Value)
>>>>>>      Next WS
>>>>>>End Sub
>>>>>>However, this leaves my text for this portion of the header at Arial 
>>>>>>Size 10 font Regular.  I would like it Arial Size 20 Bold.  How can I 
>>>>>>set it to this format of text?
>>>>>>Thanks for the help!
>>>>>>--Stacey
>>>>>
>>>>>-- 
>>>>>
>>>>>Andy Pope, Microsoft MVP - Excel
>>>>>http://www.andypope.info
>>>>
>>>>
>>>>
>>>-- 
>>>
>>>Andy Pope, Microsoft MVP - Excel
>>>http://www.andypope.info
>>
>>
>>
>
> -- 
>
> Andy Pope, Microsoft MVP - Excel
> http://www.andypope.info 


0
happyjunk (12)
2/22/2006 5:00:22 PM
Hi Stacey,

That latest code should not deselect any sheets.
If you want you can email me, off newsgroup, your workbook and I will 
have a look see.

Cheers
Andy

Stacey wrote:
> Thanks.  That did make it update prior to printing, but now I appear to have 
> another problem.
> 
> Now the problem I have is that when I select 4 of the worksheets and then
> hit Print or Print preview, it deselects the last 3 worksheets and only
> updates and prints the 1st one.  Then after using the print utilitiy the 3
> additional worksheets are no longer highlighted and active.  I'm not sure
> what's causing this, but I need to be able to update and print all of the
> selected worksheets at once.
> 
> "Andy Pope" <andy@andypope.info> wrote in message 
> news:eP2KG58NGHA.3272@tk2msftngp13.phx.gbl...
> 
>>This should do it just prior to printing.
>>
>>Private Sub Workbook_BeforePrint(Cancel As Boolean)
>>
>>    Dim WS As Worksheet
>>    For Each WS In Worksheets
>>        WS.PageSetup.RightHeader = "&20&B" & _
>>            Format(Worksheets("Time Period Info").Range("B3").Value)
>>
>>    Next WS
>>End Sub
>>
>>Cheers
>>Andy
>>
>>Stacey wrote:
>>
>>>Thanks.
>>>I'm not sure I'm entering the information quite right.  I left the 
>>>portion at the top that read "BeforePrint" but it wouldn't work when I 
>>>left it there.  However, when I deleted it, the macro will no longer 
>>>update.  What do you suggest?
>>>"Andy Pope" <andy@andypope.info> wrote in message 
>>>news:On7nV24NGHA.532@TK2MSFTNGP15.phx.gbl...
>>>
>>>
>>>>Hi Stacey,
>>>>
>>>>Good to see you worked out the combined cell problem.
>>>>
>>>>Only one sheet is active even if you have multiple sheets selected.
>>>>
>>>>This revision to your code should process all sheets.
>>>>
>>>>Sub X()
>>>>   Dim WS As Worksheet
>>>>
>>>>   For Each WS In Worksheets
>>>>       WS.PageSetup.RightHeader = _
>>>>           "&20&B" & _
>>>>Format(Worksheets("Time Period Info").Range("B3").Value)
>>>>   Next WS
>>>>End Sub
>>>>
>>>>Cheers
>>>>Andy
>>>>
>>>>Stacey wrote:
>>>>
>>>>
>>>>>Okay, last time, I promise. I often will select 4 different worksheets 
>>>>>and have them all print at once, and I thought this VBA macro would 
>>>>>update all of them (because they are all active sheets) at the same 
>>>>>time. But for some reason its JUST updating the very first one with the 
>>>>>header info.  Do you know why and how I could fix it?
>>>>>
>>>>>Thanks again for the help!
>>>>>"Andy Pope" <andy@andypope.info> wrote in message 
>>>>>news:%23MVO3$sNGHA.3988@TK2MSFTNGP09.phx.gbl...
>>>>>
>>>>>
>>>>>
>>>>>>Hi,
>>>>>>
>>>>>>You can add formatting codes to change font size and bold.
>>>>>>
>>>>>>ActiveSheet.PageSetup.RightHeader = _
>>>>>>"&20&B" & Format(Worksheets("Time Period Info").Range("B3").Value)
>>>>>>
>>>>>>Where &B Turns bold printing on or off and &nn Prints the characters 
>>>>>>that follow in the specified font size. Use a two-digit number to 
>>>>>>specify a size in points.
>>>>>>
>>>>>>Use the Help and serach for 'Formatting Codes for Headers and Footers' 
>>>>>>for a comprehensive list.
>>>>>>
>>>>>>Cheers
>>>>>>Andy
>>>>>>
>>>>>>Stacey wrote:
>>>>>>
>>>>>>
>>>>>>
>>>>>>>Hello,
>>>>>>>I wanted to reference a cell value in my spreadsheet header and found 
>>>>>>>out how to do that using this function:
>>>>>>>Private Sub Workbook_BeforePrint(Cancel As Boolean)
>>>>>>>Dim WS As Worksheet
>>>>>>>For Each WS In Worksheets
>>>>>>>  ActiveSheet.PageSetup.RightHeader = _
>>>>>>>  Format(Worksheets("Time Period Info").Range("B3").Value)
>>>>>>>     Next WS
>>>>>>>End Sub
>>>>>>>However, this leaves my text for this portion of the header at Arial 
>>>>>>>Size 10 font Regular.  I would like it Arial Size 20 Bold.  How can I 
>>>>>>>set it to this format of text?
>>>>>>>Thanks for the help!
>>>>>>>--Stacey
>>>>>>
>>>>>>-- 
>>>>>>
>>>>>>Andy Pope, Microsoft MVP - Excel
>>>>>>http://www.andypope.info
>>>>>
>>>>>
>>>>>
>>>>-- 
>>>>
>>>>Andy Pope, Microsoft MVP - Excel
>>>>http://www.andypope.info
>>>
>>>
>>>
>>-- 
>>
>>Andy Pope, Microsoft MVP - Excel
>>http://www.andypope.info 
> 
> 
> 

-- 

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
0
andy9699 (3616)
2/22/2006 9:11:49 PM
Reply:

Similar Artilces:

New to excel
Hi All, I'm new to Excel ( and to this forum :) ) and so I hope somebody may b able to help me. I've got 2 questions.... QUESTION 1 I've got a spreadsheet which takes data from one worksheet and uses i to calculate data in a second worksheet using the following code formula: =IF('4th November 2005'!B19="","nothing here dude",IF(B19<'4th Novembe 2005'!B19,"UP",IF(B19='4th November 2005'!B19,"Same",IF(B19>'4t November 2005'!B19,"DOWN")))) The problem is, when I create a new worksheet I have...

conditional formatting sort
Is there a way to sort after conditional formatting. So, if I've set up a conditional format to color my text red if the number is between 0 and 95, then I want to sort so all red numbers are at the top of the spreadsheet? Any ideas? Thanks for your time and assistance. Roberta On Thu, 25 Oct 2007 05:08:00 -0700, rrupp <rrupp@discussions.microsoft.com> wrote: You can't sort by color, but you can add a column to your query like this: SortColumn: Iif(SomeValue >= 0 and SomeValue <= 95, 1, 2) Then sort by this column. -Tom. >Is there a way to sort after condition...

Need Help, Task Start Date is wrong
I’m using MS Project 2007, have several task linked with finish to start. I have set date to schedule from, hours per day set to 8 and Working Monday thru Friday. My schedule shows Task 1 Duration 4 days, start Wed 6/2/10, Finish Mon 6/7/10 Task 2 Duration 3 days, start Mon 6/7/10, Finish Thu 6/10/10 Task 2 should have a Start Date of 6/8/10 not 6/7/10; what is causing this? Thanks in advance for your help. ...

how to convert lookup values to the "display text"
I'm using an sql code (below) which uses a few lookup fields. Unfortunately in the datasheet view, I get the "bound values" instead of the "display values". How can I change the properties for the these lookup fields so I can see the "display values" from the datasheet view? SELECT [Funding],[Date],[Description],[Company],[Expense_Type],[Amount],[Status] FROM [Form_9_Status] UNION ALL SELECT [Funding],[Date],[Description],[Company],[Expense_Type],[Amount],[Status] FROM [TDY_Status] UNION ALL SELECT [Funding],[Date],[Description],[C...

Formula without using numbers after decimal in the answer
I have a formula that derives the answer from a figure with a decimal. I don't want to use the figures after the decimal. Is there a way to just use the whole number and omit the numbers after the decimal without having to manually key in all these numbers manually? Thanks, Mustang You can use the INT function. This 'rounds down' any number to th nearest integer, e.g. if A1=2.567, a formula in B2 of =INT(A1) return 2 HTH Bruc -- swatsp0 ----------------------------------------------------------------------- swatsp0p's Profile: http://www.excelforum.com/member.php?...

Using Relative path for XML data file?
Is there a way to specify a relative path to an XML data file imported into Excel 2003? I am writing a web app that generates report data as XML for the user to download to their local machine. This data is to be consumed by an Excel reporting spreadsheet, which contains display-formatted tables and charts that are mapped to various data fields in an XML Map, which is in turn linked to the xml data file they will download. The idea is the user only needs to download the data for updates, not the whole spreadsheet. However, since I cannot predict the path where the user will store their...

random number generator
Hey is there a way to generate random numbers like you would for a draft. In other words, i have numbers 1 through 10 and generate a random order for those? Use the RAND() function. If 1-10 are in A1:A10, enter =RAND() in cell B1 and copy it down through B10. Recalc (F9), then sort by column B. Hope this helps, Hutch "pat67" wrote: > Hey is there a way to generate random numbers like you would for a > draft. In other words, i have numbers 1 through 10 and generate a > random order for those? > . > You would have to generate 10 random numbers ...

Disable Secure Sockets Layer on exchange server when using RPC over HTTP
Hi im trying to enable RPC over HTTP to enable users to establish contact to my Excahger server 2003 over the internet. Now, I dont want to use SSL (security not that important) and i am told by this article that i can disable SSL in windows registry. Quote: Note While RPC over HTTP does not require Secure Sockets Layer, you must modify the registry to enable RPC over HTTP if you do not want to use Secure Sockets Layer. Microsoft recommends that you enable and require Secure Sockets Layer for your RPC over HTTP communications. At this address: http://support.microsoft.com/?id=833401 But i ...

default values in a cell
Hello, can you help me please Cell B1 contains a complex mathematical formula which requires (in several places) a number from cell A1. Cell A1 can contain any integer number, but it is usually the same (30). I would like to be able to leave cell A1 empty, and only enter a number when it is not 30 , ie the default value of A1 is 30, unless specified. How do I go about this ? Should I look at conditional formatting, or put lots of IF functions into an already complex formula? Thank as always KK Use 2 cells Modify the complex formula to use B1 rather than A1 ( or any other un-u...

Average of absolute values of moving ranges
I'm trying to get the average of the absolute values of a set of data over 8 weeks. Each week is on a seaparate sheet so to capture the moving ranges I've been using the formula below to get my result. Is there an easier way? =AVERAGE(ABS('Week 1'!G2-'Week 2'!G2),ABS('Week 2'!G2-'Week 3'!G2),ABS('Week 3'!G2-'Week 4'!G2),ABS('Week 4'!G2-'Week 5'!G2),ABS('Week 5'!G2-'Week 6'!G2),ABS('Week 6'!G2-'Week 7'!G2),ABS('Week 7'!G2-'Week 8'!G2)) Thanks! Amy The use of t...

I need a macro
I would like a macro to do the following: I would place the the cursor in any cell and this macro would give me the average of all the entries to the left if the selected cell. EX: I place the cursor in cell M12. I want this macro on display in cell M12 the average of all the values from Col C12 to Col L12. Note that there may NOT be entries in all the cells in that range. Also the start point would always be col C .Thanks You can do this easily with a worksheet_selection event in the sheet module but you would probably want to restrict to a certain range or columns and rows or it woul...

using the journal on outlook
Once I link an email to the journal, can I still find that email in my mail box? I seem to be able to get to it only via the journal. If this is the way it is supposed to be, how do I remove it from the journal and get it back into my mail box? Am I just missing something? -- thanks, Independent Are you linking to the item or putting a copy into the journal item? Also, has the item been archived or not? "Independent" <Independent@discussions.microsoft.com> wrote in message news:868279F2-53C8-403A-97F5-604CEECD873C@microsoft.com... > Once I link an email to the journ...

How do I overlay text to a row without loosing the text in the ba.
I would like to know how to give an entire row (or column) a text overlay such as "VOID" and still be able to view the text in the underlaying row (or column). Thanks in advance. Use WordArt from the Drawing toolbar. Change the Fill to None. -- Jim Rech Excel MVP "Bruce Charles" <Bruce Charles@discussions.microsoft.com> wrote in message news:C430F6BC-1EBD-461F-A3FA-EC8592C5704C@microsoft.com... |I would like to know how to give an entire row (or column) a text overlay | such as "VOID" and still be able to view the text in the underlaying row (or | c...

How to delete a set of rows depending on Value
I have two worksheets Worksheet A 27 Columns 1600 Rows. Worksheet B 1 Column 200 Rows I need Worksheet A to look at each cell in Worksheet B, if a cell exists in Worksheet A, then I need the row deleted... Basically I have a list of Grand list of items to do, then a list of items already completed. I need to now remove all entries in the grand list that have been completed. Is this feasible or should I look at using some Unix script. It sounds like you could use VLOOKUP to find out if the value in B exists on A: http://www.officearticles.com/excel/vlookup_formulas_in_microsoft_excel....

'top' is not a valid value for the 'vertical-align' property.
Well, it is, actually, and the resulting .chm file works properly in HTML Help. But it's a bit tedious getting that wrong useless error message from VS2002 on every build. (Well, not every build, it only does it sometimes actually.) Any idea how I can persuade it to stop telling me this? -- Tim Ward Brett Ward Limited - www.brettward.co.uk ...

I need to print a word in PDF from excel.
Hi, In my excel Macro, I update several values and then open a word linked with that excel. Finally what I need to do is to print that word to PDF. I already have a PDF Printer, so I just need the code to do it. Can anybody help me? Thanks in advance. Gast=F3n. Habilita la impresora predeterminada como la PDF y ya lo puedes imprimir a al formato deseado Gracias Francisco,=20 Tienes alg=FAn codigo para ello? gracias Repitiendo: Desde el panel de impresoras asigno primero la que me interesa y despues ejecuto mi macro desde el archivo excel asignando la instruccion sig: midefaultes = a...

Let me use the Line Color icon on charts
It would speed up a lot of my work if I could use the Line Color icon on Excel charts, the same way I am able to use the Fill Color and Font Color icons. However, when I highlight any chart object, like the Plot Area, Chart Area, or a Series, the Line Color icon is disabled. -- Stuart Bratesman, Jr., MPP Muskie School of Public Service Univ. of Southern Maine Portland, Maine ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If ...

Call & Place Graphic Based on Cell Value?
Is there any way to call & place a graphic image based upon a cell value? Maybe you can look at J.E. McGimpsey's page: http://www.mcgimpsey.com/excel/lookuppics.html documike wrote: > > Is there any way to call & place a graphic image based upon a cell value? -- Dave Peterson ...

Fixing a formula to read a general format?
A few months ago I had asked a question and got this answer: Question: I have a a set of values in A1 through A100. I need to look up each value and find a match in another set of values located in C1 through C200. If a match is found then I need the formula located in column B to return the value in the same row but the next column over (D). Answer: =IF(ISNUMBER(VLOOKUP(A1,$C$1:$D$200,2,0)), VLOOKUP(A1,$C$1:$D$200,2,0), "") Copy down through A100. The formula worked fine, however the reference value and the look up value are not any more numbers but a combinatio...

Excel VBA
When you select the autofilter, the drop down list is created from the unique data in that column. Is there any way to customize this list, so the changes are permanent? For instance, I might have in one(1) column the following 4 row entries: red, blue, green (all are single cell entries) red, blue red blue, green I want my autofilter drop down list to read: red instead of red, blue, green blue red, blue green red blue, green (like normal) If I chose red from the drop down list...

MOVE TO FOLDER... only appears. I need MOVE TO FOLDER
This is very odd and I've found that I've had this problem before with not finding icons. Some I've found at the office don't appear here and vice versa, or they act differently. I have Outlook 2000 in both places! Very odd. At home, I couldn't find the HIGH IMPORTANCE icon under the list of items available in the customize. Yet I have it at work. That's the one where when the HIGH IMPORTANCE is on, it shows a depressed button state. I really need that in both places. Anyway, simple (or so I thought) - I ended up just brining the toolbar from work on a floppy, a...

Does Outlook use the DAV protocol?
I'm an Outlook Express user who wants to switch to Outlook. I received a notice from Microsoft that includes the following: "... as of June 30, 2008, Microsoft is disabling the DAV protocol and you will no longer be able to access your Hotmail Inbox via Outlook Express." Please tell me if this action by Microsoft will affect Outlook in the same manner, or am I free to make the switch. "BudV" <BudVitoff@(NO)att.(SPAM)net> wrote in message news:%230XUDi%23zIHA.2384@TK2MSFTNGP02.phx.gbl... > I'm an Outlook Express user who wants to switch to Outlook...

formula or VBA
I need to search through a column checking condtions. If the conditions are met, then I want to enter the corresponding value in the column next to it into a different cell. Basically, I want to search column A and, if the conditions are met, put the value in B into C. Would this be easier via formula or code? And maybe some starting suggestions would be nice if you don't mind. -- Thanks, Jim in a formula in col b =if(cond,a1,"") using vba for each c in range("a1:a100") if cond then c.offset(0,1)=c next -- Don Guillett SalesAid Software donaldb@281.com "...

number format with leading 0
Dose anyone know how to create a number format that would show a leading zero for the numbers 0-9. That is, so that 1 would show as 01. I can do with a text format but would like to retain the integrity of the number. Thanks Try this: Format>Cells>Number Category: Custom Type: 00 Click [OK] Does that help? *********** Regards, Ron "Darby" wrote: > Dose anyone know how to create a number format that would show a leading zero > for the numbers 0-9. That is, so that 1 would show as 01. I can do with a > text format but would like to retain the integrity...

Formula to return non-zero values in a list
I have a list of numbers in a column like so - 2 3 4 1 8 0 0 0 0 What formula would I create to always give me the a SUM of the las couple values before the zero value. The list always ends with a valu other than zero, so in this case, the '8' is the end of the list, and would like to SUM the 3 "last" values, those being 8, 1 and 4. As use this file, the zero values will automatically be updated with ne information, so I need a formula which would constantly reflect thi change -- Message posted from http://www.ExcelForum.com If you don't mind having an extra col...