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. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Need to format text in header, but value is generated using VBA
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 |
#2
|
|||
|
|||
Need to format text in header, but value is generated using VBA
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 |
#3
|
|||
|
|||
Need to format text in header, but value is generated using VBA
Exactly what I needed. Thanks again!
"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 |
#4
|
|||
|
|||
Need to format text in header, but value is generated using VBA
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" 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 |
#5
|
|||
|
|||
Need to format text in header, but value is generated using VBA
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" 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 |
#6
|
|||
|
|||
Need to format text in header, but value is generated using VBA
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 |
#7
|
|||
|
|||
Need to format text in header, but value is generated using VBA
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 |
#8
|
|||
|
|||
Need to format text in header, but value is generated using VBA
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 |
#9
|
|||
|
|||
Need to format text in header, but value is generated using VBA
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" 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 |
#10
|
|||
|
|||
Need to format text in header, but value is generated using VBA
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 |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Edit/Add record in form from cmdButton | doodle | General Discussion | 3 | December 28th, 2005 03:06 AM |
Newbie Looking for Help | Little Penny | Using Forms | 6 | December 27th, 2005 08:33 PM |
Word applies direct format on File open | Uriel | General Discussion | 16 | November 27th, 2005 07:22 PM |
Header with picture or text box | Scott | Page Layout | 14 | November 27th, 2005 03:42 AM |
Change font of part of text | John | Powerpoint | 7 | March 15th, 2005 10:10 AM |