Selection from drop down box

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,042
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,767
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. 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
2,042
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
2,042
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I don't get an error, it just will not change the header or footer.
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,042
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Don't worry about it Michael. I decided to revert to a working version and updated it from there and it is working now. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,505
Messages
5,602,063
Members
414,498
Latest member
jordanmiller7890

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top