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