A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Charts and Charting
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #11  
Old February 22nd, 2006, 06:00 PM posted to microsoft.public.excel.charting
external usenet poster
 
Posts: n/a
Default Need to format text in header, but value is generated using VBA

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" wrote in message
...
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" wrote in message
...

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" wrote in message
.. .


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



  #12  
Old February 22nd, 2006, 10:11 PM posted to microsoft.public.excel.charting
external usenet poster
 
Posts: n/a
Default Need to format text in header, but value is generated using VBA

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" wrote in message
...

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" wrote in message
...


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" wrote in message
. ..



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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Edit/Add record in form from cmdButton doodle General Discussion 3 December 28th, 2005 04:06 AM
Newbie Looking for Help Little Penny Using Forms 6 December 27th, 2005 09:33 PM
Word applies direct format on File open Uriel General Discussion 16 November 27th, 2005 08:22 PM
Header with picture or text box Scott Page Layout 14 November 27th, 2005 04:42 AM
Change font of part of text John Powerpoint 7 March 15th, 2005 11:10 AM


All times are GMT +1. The time now is 09:12 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.