Author: Oscar Cronquist Article last updated on May 06, 2019

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 spreadsheet. Is this possible?

Answer

Yes, it is possible. The animated image above demonstrates a worksheet that allows the user to select a value using a drop-down list and the header changes accordingly. The header and footer are located above and below the cell grid respectively when you print a worksheet.

The drop-down list is a regular drop-down list located in cell C2, the company name and address populates the left header. The zip code and city name populate the center header and the image is displayed on the right header.

If you download the workbook and the images are not showing then you need to create images and save them to c:\temp\ folder or change the paths and file names in column F worksheet Data to the images you want to use.

The following VBA code is event code that is saved to a worksheet module and not a regular module. Adjust cell reference A1:A4 below so it points to all of your data set values.

Event code

The event code below matches the selected company name in the drop-down list to the correct record on sheet "Data", the event macro then saves the row number of that record to a variable and uses that number to extract each value from the record.

Lastly, it saves the data to the header. The header contains three containers, left, center and right. Char(10) is the character for a new line, it separates the company name and address in the left container. It is also used in the center container to put zip code and email address on a line each.


'This event code is executed when a cell value is changed.
Private Sub Worksheet_Change(ByVal Target As Range)

'Dimension variables and data types
Dim iRow As Single

'Check if target is cell C2
If Not Intersect(Target, Range("C2")) Is Nothing Then

   'Enable error handling
   On Error Resume Next
  
   'Save row number of matching cell in cell range A1:A4 to variable iRow
   iRow = Application.Match(Range("c2"), Worksheets("Data").Range("A1:A4").Value, 0)
   
   'Check if there has been an error, MATCH function returns an error if no value is found
   If Err <> 0 Then

      'Show a message box
      MsgBox "Company name not found!"

      'Stop event code
      Exit Sub
   End If

   'Disable error handling
   On Error GoTo 0

'Save text and picture from data table to header
   With ActiveSheet
  
      'Save company name and address to left header on active worksheeet
      .PageSetup.LeftHeader = Worksheets("Data").Range("A" & iRow).Text & Chr(10) & _
      Worksheets("Data").Range("B" & iRow).Text

      'Save zip code and email address to center header on active worksheeet
      .PageSetup.CenterHeader = Worksheets("Data").Range("C" & iRow).Text & " " & _
      Worksheets("Data").Range("D" & iRow).Text & _
      Chr(10) & Worksheets("Data").Range("E" & iRow).Text
      
      'Save picture to right header on active worksheeet
      .PageSetup.RightHeaderPicture.Filename = Worksheets("Data").Range("G" & iRow).Text
   End With
End If
End Sub

Where to put the code?

  1. Copy code above.
  2. Right click on sheet name.
  3. Click "View code" to open the worksheet module in the VB Editor.
  4. Paste code to worksheet module.
  5. Exit vba editor and return to Excel.
Note, save your workbook with the file extension  *.xlsm (macro-enabled) in order to preserve the code.

Data source

The data set used to populate the header is located on worksheet "Data", the image above shows what the downloaded file will contain. Column A contains the company names.

Column B contains addresses, column C zip codes, column D city names, column E email addresses and column F contains folder paths and file names to company logos.

Final thoughts

I recommend that you use an Excel defined Table on sheet Data, it allows you to reference only the Table name and table header name. You don't need to adjust cell references when you add records, the Excel defined Table is dynamic.

For example, the following row:


iRow = Application.Match(Range("c2"), Worksheets("Data").Range("A1:A4").Value, 0)

becomes


iRow = Application.Match(Range("c2"), Worksheets("Data").Range("Table1[Company Name]").Value, 0)

Download Excel file through email

Enter your email address to receive the workbook. Note, an email will actually be sent to you.


* You will also get a weekly newsletter, unsubscribe anytime!