Use a drop down list to display data in worksheet header – VBA

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 get 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 rund 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. Press with right mouse button on on sheet name.
   
3. Press with left mouse button on "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.

Data source

The data set used to populate the header is located on worksheet "Data", the image above shows what the geted 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)

Get the Excel file

Dynamic-header.xlsm

Drop down lists category

Advanced Techniques for Conditional Formatting
Table of contents How to change cell formatting using a Drop Down list Highlight cells based on coordinates Highlight every […]

Compare data in an Excel chart using drop down lists
I will in this article demonstrate how to set up two drop down lists linked to an Excel chart, the […]

Create dependent drop down lists containing unique distinct values
Table of Contents Create dependent drop down lists containing unique distinct values - Excel 365 Create dependent drop down lists […]

Excel categories

Comments (8)