I have two option buttons on a sheet called "QUOTE". Each option button
represents a company. When their option button is selected I want there logo
to be in the header. How can I change the logo jpeg in the header using code
like I have below? I'm getting an error where objLogo is in all of the code.

Sub HeaderFooterChanger()

Dim objLogo As Variant
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

objLogo = .PageSetup.LeftHeaderPicture = _
"\\cdc.gov\private\M131\iqz9\Ad Tech\Formetco 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

objLogo = .PageSetup.LeftHeaderPicture = _
"\\cdc.gov\private\M131\iqz9\Ad Tech\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

' change logo
.LeftHeaderPicture = objLogo

' 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

First, try recording a macro when you change the picture in the header.

You'll see code like:

.PageSetup.RightHeaderPicture.Filename = "C:\folder\picture.jpg"

objLogo = .PageSetup.LeftHeaderPicture = _
"\\cdc.gov\private\M131\iqz9\Ad Tech\Formetco Logo.jpg"

So you could drop the objLogo variable completely:

..PageSetup.LeftHeaderPicture.FileName = _
"\\cdc.gov\private\M131\iqz9\Ad Tech\Formetco Logo.jpg"

and
..PageSetup.LeftHeaderPicture.filename = _
"\\cdc.gov\private\M131\iqz9\Ad Tech\Formetco Logo.jpg"

Or you could use a string variable do the work:

dim strLogo as string
...lots of code
strlogo = "\\cdc.gov\private\M131\iqz9\Ad Tech\Formetco Logo.jpg"
.pagesetup.leftheaderpicture.filename = strlogo



Ryan H wrote:

I have two option buttons on a sheet called "QUOTE". Each option button
represents a company. When their option button is selected I want there logo
to be in the header. How can I change the logo jpeg in the header using code
like I have below? I'm getting an error where objLogo is in all of the code.

Sub HeaderFooterChanger()

Dim objLogo As Variant
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

objLogo = .PageSetup.LeftHeaderPicture = _
"\\cdc.gov\private\M131\iqz9\Ad Tech\Formetco 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

objLogo = .PageSetup.LeftHeaderPicture = _
"\\cdc.gov\private\M131\iqz9\Ad Tech\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

' change logo
.LeftHeaderPicture = objLogo

' 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

you could use a boolean variable that sets it to true for one button and false
for the other. although in your code it looks like they're the same pic. maybe
i'm missing something.

if comp = true then
objLogo = .PageSetup.LeftHeaderPicture = _
"\\cdc.gov\private\M131\iqz9\Ad Tech\Formetco Logo.jpg"
else
objLogo = .PageSetup.LeftHeaderPicture = _
"\\cdc.gov\private\M131\iqz9\Ad Tech\Formetco Logo.jpg"
end if

.LeftHeaderPicture = objLogo


--


Gary Keramidas
Excel 2003


"Ryan H" wrote in message
...
I have two option buttons on a sheet called "QUOTE". Each option button
represents a company. When their option button is selected I want there logo
to be in the header. How can I change the logo jpeg in the header using code
like I have below? I'm getting an error where objLogo is in all of the code.

Sub HeaderFooterChanger()

Dim objLogo As Variant
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

objLogo = .PageSetup.LeftHeaderPicture = _
"\\cdc.gov\private\M131\iqz9\Ad Tech\Formetco 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

objLogo = .PageSetup.LeftHeaderPicture = _
"\\cdc.gov\private\M131\iqz9\Ad Tech\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

' change logo
.LeftHeaderPicture = objLogo

' 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