I need an Excel Genius !!!!!!!!!!!!!!! Headers/Footer Changes

[Ryan H]

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

 

[Dave Peterson]

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]

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

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