VBA & Macro Problems

How to Display an Excel Header When a Cell is Selected Using VBA

WPS Editor Oct 10, 2026 868 views

Question details

The user wants to display the column header (located in row 2) whenever a cell in that specific column is selected or clicked.

How to Display an Excel Header When a Cell is Selected Using VBA

Product: Excel
Device & OS: not provided
Scenario: Navigating large datasets where keeping track of the current column's header is difficult as you scroll down.
Observed behavior: Standard formulas cannot detect cell clicks, requiring a VBA event macro to trigger a message box with the header name.

Before you start

Ensure that your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled macros in your Excel Trust Center settings.

Solution 1Recommended

Use the Worksheet_SelectionChange VBA Event

Add a VBA macro to your worksheet module to detect cell clicks and pop up a message box displaying the header from row 2.

Standard Excel formulas cannot respond to user actions like clicking a cell. To achieve this interactive behavior, you must use the Worksheet_SelectionChange event in VBA. This macro detects when a single cell is selected and then retrieves the value from row 2 of that specific column.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.

Select the Target Worksheet

In the Project Explorer panel on the left, locate your workbook and double-click the specific worksheet name (e.g., Sheet1) where you want this feature to work.

Paste the VBA Code

Copy and paste the following code into the blank code window: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count = 1 Then If Target.Row > 2 And Target.Column <= Me.Cells(2, Columns.Count).End(xlToLeft).Column Then MsgBox Me.Cells(2, Target.Column).Value End If End If End Sub

Test the Macro

Close the VBA editor and return to your worksheet. Click any single cell below row 2 to see a message box pop up displaying the corresponding column header.

Use the Worksheet_SelectionChange VBA Event

Customizing the Header Row: If your headers are in row 1 instead of row 2, change the '2' in both 'Me.Cells(2, ...)' and 'Target.Row > 2' to '1' in the provided code.

Advanced Spreadsheet Automation

Use WPS Spreadsheet to Run Excel VBA Macros

WPS Office provides robust support for VBA and macros, allowing you to run your Excel scripts, automate repetitive tasks, and trigger events like Worksheet_SelectionChange seamlessly in an intuitive environment.

1. Download and Install: Download WPS Office Free and launch the WPS Spreadsheet application.
2. Open Your Workbook: Open your Excel Macro-Enabled Workbook (.xlsm) containing your dataset.
3. Access the VBA Editor: Navigate to the 'Developer' tab on the top ribbon and click on 'Visual Basic' or 'Macros' to access the editor.
4. Apply the Code: Paste your Worksheet_SelectionChange code into the desired worksheet module to instantly activate the cell selection event.

Fully compatible with Microsoft Excel VBA scripts and macro-enabled workbooks (.xlsm)Lightweight and fast spreadsheet processing for large datasetsFamiliar user interface with no learning curve requiredFree access to essential office tools, templates, and advanced features

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't the macro work when I select multiple cells?

The VBA code includes the condition `If Target.Cells.Count = 1 Then`. This ensures the message box only triggers when a single cell is selected, preventing your screen from being overwhelmed by multiple pop-ups.

Can I display the header in a specific static cell instead of a message box?

Yes, you can modify the code. Instead of using `MsgBox`, you can output the value to a fixed cell (like A1) by changing the relevant line to: `Range("A1").Value = Me.Cells(2, Target.Column).Value`.

How do I save my workbook after adding this VBA code?

You must save your file as an Excel Macro-Enabled Workbook (.xlsm). If you attempt to save it as a standard .xlsx file, Excel will remove all VBA code from the workbook.

How can I disable this feature if I no longer need it?

You can either delete the VBA code entirely from the worksheet module in the VBA editor, or temporarily disable macros in your Excel Trust Center settings.

Excel & Data Problems