Excel VBA: Create Table Column Names from a Drop-Down List
Question details
The user needs to automatically update worksheet columns or table headings when a value is selected 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.
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.
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.
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.
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
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.
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.

Write the selected value to a normal worksheet header
Use this simpler version when the destination is a cell or header row rather than an Excel Table object.
Run a button macro for multiple selected names
Use a button-triggered procedure when several worksheet headings must be rebuilt from a source list or when automatic updates are not desirable.
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. Create the drop-down list: Select the source cell, open Data > Validation, choose List, and enter the source range or list values.
- 2. Open the macro editor: Open the Developer tools and select the VBA editor in a WPS version that supports VBA macros.
- 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. Update the references: Replace the sample cell, worksheet, table, and column names with the names used in your workbook.
- 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.

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.




