Selection from drop down box

[dpaton05]

I have a drop down box on a sheet called Start_here with 2 options and each option is a town. When the option is selected and a Continue button is pressed I need a header and footer to be loaded onto the sheet quote_sheet depending on the town selected.

• The headers and footers are images and are stored on a sheet called Sheet2.
• The image names are
  • Town 1
    • ImgWestHeader
    • ImgWestFooter
  • Town 2
    • ImgRivHeader
    • ImgDYFooter

This is the code that I have tried to find in various places online but it won't work. Could someone help me with the correct code please?

Private Sub cmdContinue_Click()
    Call Header_footer
    With ThisWorkbook
        .Worksheets("quote_sheet").Visible = True
        .Worksheets("quote_sheet").Activate
        .Worksheets("Start_here").Visible = xlVeryHidden
    End With
End Sub

Sub Header_footer()
Dim city As String
    'The city variable becomes the value selected on the Start_here sheet at H9
    city = ThisWorkbook.Worksheets("Start_here").Range("H9").Value
    
    Select Case city
        Case Is = "Town1"
            Call Town1_header_footer
        Case Is = "Town2"
            Call Town2_header_footer
    End Select
End Sub

Sub Town1_header_footer()
Dim printWorksheet As Worksheet, logoShape As Shape, tempImageFile As String
        'The worksheet on which the print page setup will apply
        Set printWorksheet = ThisWorkbook.Worksheets("quote_sheet")
        'The name of shape to be used in page setup
        Set logoShape = ThisWorkbook.Worksheets("sheet2").Shapes("ImgWestHeader")

        'Save the shape as a temporary image
        tempImageFile = Environ("temp") & "\image.jpg"
        Save_Object_As_Picture logoShape, tempImageFile
            With printWorksheet.PageSetup
                .CenterHeaderPicture.fileName = tempImageFile
                .CenterHeader = "&G"
 '               .CenterFooter = 

            End With
        Kill tempImageFile
End Sub


Sub Town2_header_footer()
Dim printWorksheet As Worksheet, logoShape As Shape, tempImageFile As String
Dim footshape As Shape
        'The worksheet on which the print page setup will apply
        Set printWorksheet = ThisWorkbook.Worksheets("quote_sheet")
        'The sheet location and name of shape to be used in page setup
            
        Set logoShape = ThisWorkbook.Worksheets("sheet2").Shapes("ImgRivHeader")
        Set footshape = ThisWorkbook.Worksheets("sheet2").Shapes("ImgDYFooter")
        'Save the shape as a temporary image
        tempImageFile = Environ("temp") & "\image.jpg"
        Save_Object_As_Picture logoShape, tempImageFile
            With printWorksheet.PageSetup
                .CenterHeaderPicture.fileName = tempImageFile
                .CenterHeader = "&G"
            End With
        Kill tempImageFile
        
        tempImageFile = Environ("temp") & "\image.jpg"
        Save_Object_As_Picture footshape, tempImageFile
            With printWorksheet.PageSetup
                .CenterFooterPicture.fileName = tempImageFile
                .CenterFooter = "&G"
            End With
        Kill tempImageFile
        
        
End Sub
Private Sub Save_Object_As_Picture(saveObject As Object, imageFileName As String)
    Dim temporaryChart As ChartObject
    Application.ScreenUpdating = False
    saveObject.CopyPicture xlScreen, xlPicture
    Set temporaryChart = ActiveSheet.ChartObjects.Add(0, 0, saveObject.Width + 1, saveObject.Height + 1)
    With temporaryChart
        .Activate
        .border.LineStyle = xlLineStyleNone      'No border
        .Chart.Paste
        .Chart.Export imageFileName
        .Delete
    End With
    Application.ScreenUpdating = True
    Set temporaryChart = Nothing
End Sub

 

[Michael M]

Dave

but it won't work

...tells us nothing !
Are the "town" names correct here...

Select Case city
        Case Is = "Town1"
            Call Town1_header_footer
        Case Is = "Town2"
            Call Town2_header_footer
    End Select
End Sub

also, if you step through the code, does the correct town header get called ?

 

[dpaton05]

Sorry Michael,

I will try and explain it a little better.

• At the moment, I have the header and footer located on quote_sheet for town 2
• If I select town 1 on the Start_here sheet and press continue, the town 2 header and footer doesn't change to the town1 header and footer but remains the same

The header and footer for town 1 are on sheet 2 and they are called ImgWestHeader and ImgWestFooter

The header and footer for town 2 are on sheet 2 and are called ImgRivHeader and ImgDYFooter

 

[dpaton05]

I don't get an error, it just will not change the header or footer.

 

[dpaton05]

Don't worry about it Michael. I decided to revert to a working version and updated it from there and it is working now.