Selection from drop down box

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,025
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?

Code:
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

Well-known Member
Joined
Oct 27, 2005
Messages
17,959
Office Version
2013
Platform
Windows
Dave
but it won't work
...tells us nothing !
Are the "town" names correct here...

Code:
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

Well-known Member
Joined
Aug 14, 2018
Messages
1,025
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

Well-known Member
Joined
Aug 14, 2018
Messages
1,025
I don't get an error, it just will not change the header or footer.
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,025
Don't worry about it Michael. I decided to revert to a working version and updated it from there and it is working now. :)
 

Forum statistics

Threads
1,077,828
Messages
5,336,622
Members
399,093
Latest member
chado4250

Some videos you may like

This Week's Hot Topics

Top