VBA & Macro Problems

Excel VBA: Create Table Column Names from a Drop-Down List

Adam DavisAdam Davis Nov 12, 2026 870 views

Question details

The user needs to automatically update worksheet columns or table headings when a value is selected from a drop-down list.

Excel VBA: Create Table Column Names from a Drop-Down List
Product
Microsoft Excel VBA or WPS Spreadsheets with VBA support
Device & OS
not provided
Scenario
A worksheet contains a drop-down list, and the selected value should be written to a destination heading or used to update table columns.
Observed behavior
The selected drop-down value does not automatically update the destination worksheet or table heading.
Before you start

Confirm the drop-down cell address, destination worksheet name, and target table or header range. Save the workbook as a macro-enabled .xlsm file and verify that macros are permitted before testing the event.

Solution 1Recommended

Use Worksheet_Change to update a destination table heading

Use this event-driven approach when one drop-down cell should immediately update a specific heading or table column name.

Place the event code in the worksheet module that contains the drop-down cell, not in a standard VBA module. The example assumes the list is in Sheet1 cell A2 and the destination table is on Sheet2.

If the destination is a normal header range rather than an Excel Table, replace the ListColumns line with a direct range assignment such as .Range("A3").Value = Me.Range("A2").Value.

1
Open the worksheet code window

In Excel, press Alt+F11, locate the worksheet containing the drop-down under Microsoft Excel Objects, and double-click that sheet. In WPS Spreadsheets, open the VBA editor from the Developer tools when VBA support is enabled.

2
Insert the change event

Choose Worksheet from the left procedure list and Change from the right procedure list, then enter this code: Private Sub Worksheet_Change(ByVal Target As Range)\n Const DROP_CELL As String = "$A$2"\n If Intersect(Target, Me.Range(DROP_CELL)) Is Nothing Then Exit Sub\n\n On Error GoTo CleanUp\n Application.EnableEvents = False\n\n With ThisWorkbook.Worksheets("Sheet2").ListObjects("Table1")\n .ListColumns(1).Name = Me.Range(DROP_CELL).Value\n End With\n\nCleanUp:\n Application.EnableEvents = True\nEnd Sub

3
Replace the sample references

Change $A$2 to the actual drop-down cell, Sheet2 to the destination sheet, Table1 to the table name, and ListColumns(1) to the column position that should receive the selected name.

4
Test the drop-down

Return to the worksheet and choose another value from the drop-down in cell A2. The first column heading in Table1 should be renamed automatically.

Use Worksheet_Change to update a destination table heading
Prevent repeated event triggers: Application.EnableEvents = False prevents the macro from calling itself when it changes the destination. The CleanUp section turns events back on even if an error occurs.

Automate Drop-Down-Based Table Headings in WPS Spreadsheets

WPS Spreadsheets can reproduce this workflow with a drop-down list, worksheet event, and VBA-compatible macro environment where macro support is available. Use the VBA editor to connect the selection cell to a destination heading or table column, then save the workbook in a compatible macro-enabled format.

  1. 1. Create the drop-down list: Select the source cell, open Data > Validation, choose List, and enter the source range or list values.
  2. 2. Open the macro editor: Open the Developer tools and select the VBA editor in a WPS version that supports VBA macros.
  3. 3. Add the worksheet event: Open the worksheet module containing the list cell and add a Worksheet_Change procedure that writes the selected value to the target heading or table column.
  4. 4. Update the references: Replace the sample cell, worksheet, table, and column names with the names used in your workbook.
  5. 5. Save and test the workbook: Save the file as .xlsm when macro preservation is required, enable macros when prompted, and select a new drop-down value to verify the heading update.
Automatically update table headings when a drop-down selection changesSupport familiar spreadsheet formulas, data validation, and macro workflowsOpen and edit common Microsoft Excel .xlsx and macro-enabled .xlsm filesUse a lightweight Office interface for migrating spreadsheet automation projects
microsoft office alternative - wps office

Frequently Asked Questions

Where should the Worksheet_Change VBA code be placed?

Place it in the worksheet module that contains the drop-down cell. In the VBA editor, expand Microsoft Excel Objects or the corresponding WPS workbook objects and double-click that worksheet; do not place event code in a standard module.

Why does the macro not run after I select a new list value?

Check that macros are enabled, the event code is in the correct worksheet module, and the monitored address matches the drop-down cell. If another macro disabled events, run Application.EnableEvents = True in the Immediate window and test again.

How do I update a normal header cell instead of an Excel Table column?

Replace the ListObjects code with a direct assignment such as ThisWorkbook.Worksheets("Sheet2").Range("A3").Value = Me.Range("A2").Value. Update A2, Sheet2, and A3 to match your workbook.

Can one drop-down selection update several table columns?

Yes. Extend the Worksheet_Change procedure with additional destination assignments, or use the selected value to find a matching source row and write each returned name into the required header cells. Use unique table headings to avoid duplicate-name errors.

Why is the selected value not accepted as an Excel table column name?

Table headings must be unique and cannot be blank. Check the selected text for duplicates or empty values, and use a validation list containing distinct, nonblank names before assigning the value to ListColumns(index).Name.