R
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
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