Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
I need an Excel Genius !!!!!!!!!!!!!!! Headers/Footer Changes
I think I need some work around code. I have two workbooks named QG and
Add-In. Add-In contains all the code to manipulate data in QG. I have two option buttons in Sheets("QUOTE") in QG. Each option button represents a company. When an option button is clicked the code below located in Add-In is called, which changes the Header and Footer to represent that company. The code works fine, but there is a fault. If the Logo is moved to a different folder the code fails. Is there a way to store the two company logos (.jpeg) in one of the workbooks so that when one of the option buttons are clicked the appropriate company logo will be displayed in the left header? Sub HeaderFooterChanger() Dim strLogoFileName As String Dim strRightHeader As String Dim strCenterFooter As String Application.ScreenUpdating = False With Sheets("QUOTE") ' which option button is true ' Constants Enumeration: xlOn = 1 and xlOff = -4146 Select Case xlOn ' Ad Tech Case .OptionButtons("optAdTech").Value strLogoFileName = "\\cdc.gov\private\M131\iqz9\Ad Tech\Temporary\Ad Tech Logo.jpg" strRightHeader = "&""-,Bold""QUOTATION / SALES AGREEMENT" & Chr(10) & _ "&""-,Regular""Ad-Tech International, Inc." & Chr(10) & _ "2963 Pleasant Hill Road" & Chr(10) & _ "Duluth, GA 30096" & Chr(10) & _ "PH 770-209-9102, Fax 770-209-0465" & Chr(10) & _ "&D" strCenterFooter = "Ad-Tech International, Inc." & Chr(10) & _ "www.adtechintl.com" ' Formetco Case .OptionButtons("optFormetco").Value strLogoFileName = "\\cdc.gov\private\M131\iqz9\Ad Tech\Temporary\Formetco Logo.jpg" strRightHeader = "&""-,Bold""QUOTATION / SALES AGREEMENT" & Chr(10) & _ "&""-,Regular""Formetco" & Chr(10) & _ "2963 Pleasant Hill Road" & Chr(10) & _ "Duluth, GA 30096" & Chr(10) & _ "PH 1-800-Formetco, Fax ???-???-????" & Chr(10) & _ "&D" strCenterFooter = "Formetco" & Chr(10) & _ "www.formetco.com" End Select ' change header/footer properties With .PageSetup ' remove old header .LeftHeader = "" ' change logo .LeftHeaderPicture.Filename = strLogoFileName ' ensure header picture can be visible .LeftHeader = "&G" ' change header/footer .RightHeader = strRightHeader .CenterFooter = strCenterFooter End With End With Application.ScreenUpdating = True End Sub -- Cheers, Ryan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
I need an Excel Genius !!!!!!!!!!!!!!! Headers/Footer Changes
This is a suggestion you may not want...
Why not just make sure that the pictures are in the same folder as the addin. Then you could use: strLogoFileName = thisworkbook.path & "\" & "Ad Tech Logo.jpg" ====== I didn't do much looking, but it looks (from recording a macro) that the picture in the header/footer has to come from an external file. (This may be completely wrong, though. I'll await more responses!) There are ways of saving a range/chart as a picture. Harald Staff's routine at David McRitchie's site: http://www.mvps.org/dmcritchie/excel/xl2gif.htm Ryan H wrote: I think I need some work around code. I have two workbooks named QG and Add-In. Add-In contains all the code to manipulate data in QG. I have two option buttons in Sheets("QUOTE") in QG. Each option button represents a company. When an option button is clicked the code below located in Add-In is called, which changes the Header and Footer to represent that company. The code works fine, but there is a fault. If the Logo is moved to a different folder the code fails. Is there a way to store the two company logos (.jpeg) in one of the workbooks so that when one of the option buttons are clicked the appropriate company logo will be displayed in the left header? Sub HeaderFooterChanger() Dim strLogoFileName As String Dim strRightHeader As String Dim strCenterFooter As String Application.ScreenUpdating = False With Sheets("QUOTE") ' which option button is true ' Constants Enumeration: xlOn = 1 and xlOff = -4146 Select Case xlOn ' Ad Tech Case .OptionButtons("optAdTech").Value strLogoFileName = "\\cdc.gov\private\M131\iqz9\Ad Tech\Temporary\Ad Tech Logo.jpg" strRightHeader = "&""-,Bold""QUOTATION / SALES AGREEMENT" & Chr(10) & _ "&""-,Regular""Ad-Tech International, Inc." & Chr(10) & _ "2963 Pleasant Hill Road" & Chr(10) & _ "Duluth, GA 30096" & Chr(10) & _ "PH 770-209-9102, Fax 770-209-0465" & Chr(10) & _ "&D" strCenterFooter = "Ad-Tech International, Inc." & Chr(10) & _ "www.adtechintl.com" ' Formetco Case .OptionButtons("optFormetco").Value strLogoFileName = "\\cdc.gov\private\M131\iqz9\Ad Tech\Temporary\Formetco Logo.jpg" strRightHeader = "&""-,Bold""QUOTATION / SALES AGREEMENT" & Chr(10) & _ "&""-,Regular""Formetco" & Chr(10) & _ "2963 Pleasant Hill Road" & Chr(10) & _ "Duluth, GA 30096" & Chr(10) & _ "PH 1-800-Formetco, Fax ???-???-????" & Chr(10) & _ "&D" strCenterFooter = "Formetco" & Chr(10) & _ "www.formetco.com" End Select ' change header/footer properties With .PageSetup ' remove old header .LeftHeader = "" ' change logo .LeftHeaderPicture.Filename = strLogoFileName ' ensure header picture can be visible .LeftHeader = "&G" ' change header/footer .RightHeader = strRightHeader .CenterFooter = strCenterFooter End With End With Application.ScreenUpdating = True End Sub -- Cheers, Ryan -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
I need an Excel Genius !!!!!!!!!!!!!!! Headers/Footer Changes
That's what I figured I'd have to do, but I wasn't sure if there was another
way. Thanks for the reply! -- Cheers, Ryan "Dave Peterson" wrote: This is a suggestion you may not want... Why not just make sure that the pictures are in the same folder as the addin. Then you could use: strLogoFileName = thisworkbook.path & "\" & "Ad Tech Logo.jpg" ====== I didn't do much looking, but it looks (from recording a macro) that the picture in the header/footer has to come from an external file. (This may be completely wrong, though. I'll await more responses!) There are ways of saving a range/chart as a picture. Harald Staff's routine at David McRitchie's site: http://www.mvps.org/dmcritchie/excel/xl2gif.htm Ryan H wrote: I think I need some work around code. I have two workbooks named QG and Add-In. Add-In contains all the code to manipulate data in QG. I have two option buttons in Sheets("QUOTE") in QG. Each option button represents a company. When an option button is clicked the code below located in Add-In is called, which changes the Header and Footer to represent that company. The code works fine, but there is a fault. If the Logo is moved to a different folder the code fails. Is there a way to store the two company logos (.jpeg) in one of the workbooks so that when one of the option buttons are clicked the appropriate company logo will be displayed in the left header? Sub HeaderFooterChanger() Dim strLogoFileName As String Dim strRightHeader As String Dim strCenterFooter As String Application.ScreenUpdating = False With Sheets("QUOTE") ' which option button is true ' Constants Enumeration: xlOn = 1 and xlOff = -4146 Select Case xlOn ' Ad Tech Case .OptionButtons("optAdTech").Value strLogoFileName = "\\cdc.gov\private\M131\iqz9\Ad Tech\Temporary\Ad Tech Logo.jpg" strRightHeader = "&""-,Bold""QUOTATION / SALES AGREEMENT" & Chr(10) & _ "&""-,Regular""Ad-Tech International, Inc." & Chr(10) & _ "2963 Pleasant Hill Road" & Chr(10) & _ "Duluth, GA 30096" & Chr(10) & _ "PH 770-209-9102, Fax 770-209-0465" & Chr(10) & _ "&D" strCenterFooter = "Ad-Tech International, Inc." & Chr(10) & _ "www.adtechintl.com" ' Formetco Case .OptionButtons("optFormetco").Value strLogoFileName = "\\cdc.gov\private\M131\iqz9\Ad Tech\Temporary\Formetco Logo.jpg" strRightHeader = "&""-,Bold""QUOTATION / SALES AGREEMENT" & Chr(10) & _ "&""-,Regular""Formetco" & Chr(10) & _ "2963 Pleasant Hill Road" & Chr(10) & _ "Duluth, GA 30096" & Chr(10) & _ "PH 1-800-Formetco, Fax ???-???-????" & Chr(10) & _ "&D" strCenterFooter = "Formetco" & Chr(10) & _ "www.formetco.com" End Select ' change header/footer properties With .PageSetup ' remove old header .LeftHeader = "" ' change logo .LeftHeaderPicture.Filename = strLogoFileName ' ensure header picture can be visible .LeftHeader = "&G" ' change header/footer .RightHeader = strRightHeader .CenterFooter = strCenterFooter End With End With Application.ScreenUpdating = True End Sub -- Cheers, Ryan -- Dave Peterson . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding Headers and Footer to an Entire Workbook?? | Excel Discussion (Misc queries) | |||
saving a sheet as a web page and retaining the headers and footer. | Excel Discussion (Misc queries) | |||
Headers and Footer on the Chart do not work?????? | Charts and Charting in Excel | |||
How do I make the custom headers and footer repeat on every works. | Excel Worksheet Functions | |||
spesific fonts for headers&footer is Excel | Excel Programming |