VBA & Macro Problems

How to Show or Hide Excel VBA Buttons Only in Specific Rows

Steven Parker Oct 21, 2026 868 views

Question details

The user needs to display and position Excel VBA macro buttons only when the active cell falls within predefined, noncontiguous row ranges, while hiding the buttons for all other cell selections.

How to Show Excel VBA Buttons Only in Specific Rows

Product: Excel
Device & OS: not provided
Scenario: Creating dynamic forms or dashboards where certain macro buttons should only be accessible and visible during data entry in specific rows.
Observed behavior: The goal is to automate button visibility and positioning based on the active cell, ensuring buttons appear for specific ranges (like 6:13 or 15:20) and disappear for invalid or multi-cell selections.

Before you start

Ensure you have saved your workbook as a Macro-Enabled Workbook (.xlsm) and that the Developer tab is enabled to access the Visual Basic Editor.

Solution 1Recommended

Use Worksheet_SelectionChange Event to Toggle Button Visibility

Trigger a VBA macro automatically when the user changes their selection, checking if the active cell falls within the allowed row ranges.

By using the Intersect method within the Worksheet_SelectionChange event, you can verify if the user's active cell is within your target noncontiguous ranges (e.g., rows 6:13, 15:20, 22:24). If the selection matches, the code makes the buttons visible and moves them to the active row; otherwise, it hides them.

Open the VBA Editor

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

Access the Target Worksheet

In the Project Explorer panel on the left, double-click the specific worksheet where your buttons are located (e.g., Sheet1).

Add the SelectionChange Event

From the left dropdown at the top of the code window, select 'Worksheet'. The right dropdown will default to 'SelectionChange'. This creates an empty Private Sub Worksheet_SelectionChange(ByVal Target As Range) block.

Write the Intersect Logic

Inside the sub, add a condition to ignore multi-cell selections: If Target.Cells.Count > 1 Then Exit Sub. Next, use the Intersect function to test the active row: If Not Intersect(Target, Range("6:13,15:20,22:24,26:28,36:37,39:41")) Is Nothing Then.

Toggle Shape Visibility and Position

Inside the If block, make the button visible and align it: ActiveSheet.Shapes("YourButtonName").Visible = True, followed by ActiveSheet.Shapes("YourButtonName").Top = Target.Top. Add an Else block to hide the button: ActiveSheet.Shapes("YourButtonName").Visible = False.

Use Worksheet_SelectionChange Event to Toggle Button Visibility

Identify Button Names: Make sure to replace 'YourButtonName' in the code with the exact name of your shape or Form Control button, which you can find in the Name Box next to the formula bar when the button is selected.

Advanced Spreadsheet Macros

Create and Manage VBA Macros Easily in WPS Spreadsheet

WPS Spreadsheet fully supports VBA and Macros, allowing you to run dynamic scripts like SelectionChange events seamlessly. You can easily insert shapes, assign macros, and write VBA code within a highly compatible and familiar interface.

1. Open Your File in WPS Office: Launch WPS Spreadsheet and open your Macro-Enabled Workbook.
2. Enable the Developer Tab: If not visible, go to Menu > Options > Customize Ribbon and check the 'Developer' tab.
3. Insert a Form Button: Navigate to the Developer tab, click 'Insert', and select a Button control to place on your worksheet.
4. Access the VBA Editor: Click 'Visual Basic' in the Developer tab (or press Alt + F11) to write or paste your SelectionChange event code.

Full support for Excel VBA code and Macro-Enabled Workbooks (.xlsm).Seamless compatibility with Microsoft Excel formulas, formats, and shapes.Built-in Developer tools to easily insert buttons and manage form controls.Lightweight application with high processing speed for complex automation scripts.

microsoft office alternative - wps office

Frequently Asked Questions

How do I prevent the macro from running when multiple cells are selected?

Add a condition at the very beginning of your SelectionChange event code: If Target.Cells.Count > 1 Then Exit Sub. This ensures the script stops executing and prevents errors if a user highlights a large range of cells at once.

Can I automatically move the button to align with the active cell's row?

Yes. Once you have verified the active cell is in the valid range, you can align the button's vertical position by matching its Top property to the Target's Top property. Use the code: ActiveSheet.Shapes("ButtonName").Top = Target.Top.

Why aren't my macro buttons hiding when I reopen the Excel file?

Ensure you have saved the file as a Macro-Enabled Workbook (.xlsm). Additionally, check your macro security settings in the Developer tab (Macro Security) to ensure macros are enabled, or make sure you click 'Enable Content' when prompted upon opening the file.

Excel & Data Problems