Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Visual Basic Question
I have several worksheets that I need to inset the page
number into the center footer. I would use the &page number but my page numbers are in the format of "1-1, 1-2, 1-3, etc." Excel does not allow this so I am trying to write a macro that asks me the page number and inserts it into the center footer position. I have that part of the macro written but I cannot format the font, size and boldness of the page number. Can anyone give me a hand. Here is the macro I have currently tried and I want it to appear Arial Font size 4, and Bolded. Sub PageNumber() ActiveSheet.PageSetup.CenterFooter = InputBox("Enter page Number") End Sub Any ideas or suggestions would be greatly appreciated. Thanks in adance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Visual Basic Question
Craig;
Here ya go; Sub PageNumber() ActiveSheet.PageSetup.CenterFooter = InputBox ("Enterpage Number") With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "&""Arial Black,Bold""&11 44" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = -3 .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With End Sub -----Original Message----- I have several worksheets that I need to inset the page number into the center footer. I would use the &page number but my page numbers are in the format of "1-1, 1- 2, 1-3, etc." Excel does not allow this so I am trying to write a macro that asks me the page number and inserts it into the center footer position. I have that part of the macro written but I cannot format the font, size and boldness of the page number. Can anyone give me a hand. Here is the macro I have currently tried and I want it to appear Arial Font size 4, and Bolded. Sub PageNumber() ActiveSheet.PageSetup.CenterFooter = InputBox("Enter page Number") End Sub Any ideas or suggestions would be greatly appreciated. Thanks in adance. . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Visual Basic Question
I tried this but I enter the page number and the page
number stays at 44. Which appears in .CenterFooter = "&""Arial Black,Bold""&11 44" of your formula. I am wondering if there is even a way to get this to work with the InputBox feature. I have tried to use the InputBox function where the "44" appears but cannot get it to work. Thanks for your help!! -----Original Message----- Craig; Here ya go; Sub PageNumber() ActiveSheet.PageSetup.CenterFooter = InputBox ("Enterpage Number") With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "&""Arial Black,Bold""&11 44" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = -3 .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With End Sub -----Original Message----- I have several worksheets that I need to inset the page number into the center footer. I would use the &page number but my page numbers are in the format of "1-1, 1- 2, 1-3, etc." Excel does not allow this so I am trying to write a macro that asks me the page number and inserts it into the center footer position. I have that part of the macro written but I cannot format the font, size and boldness of the page number. Can anyone give me a hand. Here is the macro I have currently tried and I want it to appear Arial Font size 4, and Bolded. Sub PageNumber() ActiveSheet.PageSetup.CenterFooter = InputBox("Enter page Number") End Sub Any ideas or suggestions would be greatly appreciated. Thanks in adance. . . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Visual Basic Question
Sub PromptForFooter()
Dim strFooter As String strFooter = InputBox("Enter Page Number:") With ActiveSheet.PageSetup .CenterFooter = "&""Arial,Bold""&4 " & strFooter End With End Sub You did say you wanted Arial, Bold, 4 point, right? If not, change the 4 but be *sure* to leave a space between it and the closing quote or VBA will take the first part of your page number and treat it as part of the Font size. When I ran this code without the space, a Font size of 11 and strFooter equal to "2-1" I got a font size of 112 and a Footer of "-1" in PrintPreview. An interesting result, but not what you want. :-) This wouldn't be an issue if the value of strFooter began with a non-numeric value. Hope this helps, -- George Nicholson Remove 'Junk' from return address. "Craig" wrote in message ... I tried this but I enter the page number and the page number stays at 44. Which appears in .CenterFooter = "&""Arial Black,Bold""&11 44" of your formula. I am wondering if there is even a way to get this to work with the InputBox feature. I have tried to use the InputBox function where the "44" appears but cannot get it to work. Thanks for your help!! -----Original Message----- Craig; Here ya go; Sub PageNumber() ActiveSheet.PageSetup.CenterFooter = InputBox ("Enterpage Number") With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "&""Arial Black,Bold""&11 44" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = -3 .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With End Sub -----Original Message----- I have several worksheets that I need to inset the page number into the center footer. I would use the &page number but my page numbers are in the format of "1-1, 1- 2, 1-3, etc." Excel does not allow this so I am trying to write a macro that asks me the page number and inserts it into the center footer position. I have that part of the macro written but I cannot format the font, size and boldness of the page number. Can anyone give me a hand. Here is the macro I have currently tried and I want it to appear Arial Font size 4, and Bolded. Sub PageNumber() ActiveSheet.PageSetup.CenterFooter = InputBox("Enter page Number") End Sub Any ideas or suggestions would be greatly appreciated. Thanks in adance. . . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Thanks George
George:
Thanks a ton. This works great! -----Original Message----- Sub PromptForFooter() Dim strFooter As String strFooter = InputBox("Enter Page Number:") With ActiveSheet.PageSetup .CenterFooter = "&""Arial,Bold""&4 " & strFooter End With End Sub You did say you wanted Arial, Bold, 4 point, right? If not, change the 4 but be *sure* to leave a space between it and the closing quote or VBA will take the first part of your page number and treat it as part of the Font size. When I ran this code without the space, a Font size of 11 and strFooter equal to "2-1" I got a font size of 112 and a Footer of "-1" in PrintPreview. An interesting result, but not what you want. :-) This wouldn't be an issue if the value of strFooter began with a non-numeric value. Hope this helps, -- George Nicholson Remove 'Junk' from return address. "Craig" wrote in message ... I tried this but I enter the page number and the page number stays at 44. Which appears in .CenterFooter = "&""Arial Black,Bold""&11 44" of your formula. I am wondering if there is even a way to get this to work with the InputBox feature. I have tried to use the InputBox function where the "44" appears but cannot get it to work. Thanks for your help!! -----Original Message----- Craig; Here ya go; Sub PageNumber() ActiveSheet.PageSetup.CenterFooter = InputBox ("Enterpage Number") With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "&""Arial Black,Bold""&11 44" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = -3 .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With End Sub -----Original Message----- I have several worksheets that I need to inset the page number into the center footer. I would use the &page number but my page numbers are in the format of "1-1, 1- 2, 1-3, etc." Excel does not allow this so I am trying to write a macro that asks me the page number and inserts it into the center footer position. I have that part of the macro written but I cannot format the font, size and boldness of the page number. Can anyone give me a hand. Here is the macro I have currently tried and I want it to appear Arial Font size 4, and Bolded. Sub PageNumber() ActiveSheet.PageSetup.CenterFooter = InputBox ("Enter page Number") End Sub Any ideas or suggestions would be greatly appreciated. Thanks in adance. . . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Visual basic 6.5 question using combo box and call procedure. | Excel Discussion (Misc queries) | |||
Visual Basic Question | Excel Worksheet Functions | |||
visual basic coding question | Excel Discussion (Misc queries) | |||
Visual Basic Question | Excel Discussion (Misc queries) | |||
changing the visual basic in office 2003 to visual studio net | Excel Discussion (Misc queries) |