Author: Oscar Cronquist Article last updated on May 02, 2012

Aynsley Wall asks:

I have a spreadsheet that I use for 3 different companies.
What i would really like to do is have a drop down menu with the three company names: eg: Mcdonalds, Pizza Hut, Subway and then when i choose which company the spreadsheet will be for then all the contact information and logo will appear as a header on the top of the spread sheet. is this possible?



VBA code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iRow As Single
If Not Intersect(Target, Range("C2")) Is Nothing Then
   On Error Resume Next
   iRow = Application.Match(Range("c2"), Worksheets("Data").Range("A1:A4").Value, 0)
   If Err <> 0 Then
      MsgBox "Company name not found!"
      Exit Sub
   End If
   On Error GoTo 0
   With ActiveSheet
      .PageSetup.LeftHeader = Worksheets("Data").Range("A" & iRow).Text & Chr(10) & _
      Worksheets("Data").Range("B" & iRow).Text
      .PageSetup.CenterHeader = Worksheets("Data").Range("C" & iRow).Text & " " & _
      Worksheets("Data").Range("D" & iRow).Text & _
      Chr(10) &amp; Worksheets("Data").Range("E" &amp; iRow).Text
      .PageSetup.RightHeaderPicture.Filename = Worksheets("Data").Range("G" & iRow).Text
   End With
End If
End Sub

Copy vba code and paste in sheet module

  1. Copy code
  2. Right click on sheet name
  3. Click "View code"
  4. Paste code into sheet module
  5. Exit vba editor

Sheet "Data":

Column F contains folder paths and file names to company logos.

Download excel *.xlsm file

Dynamic header.xlsm