VBA & Macro Problems

Show and Hide Excel Worksheets with Option Buttons

Elise WilliamsElise Williams Nov 14, 2026 869 views

Question details

Use two form option buttons to display different groups of Excel worksheets.

Show and Hide Excel Worksheets with Option Buttons
Product
Microsoft Excel
Device & OS
not provided
Scenario
Each option button should show its corresponding group of worksheets and hide the other group.
Observed behavior
Selecting an option should leave its associated worksheets visible and hide the worksheets associated with the other option.
Before you start

Use a desktop version of Excel that supports VBA, and identify the exact names of the worksheets in each group. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) and enable macros only if you trust the file.

Solution 1Recommended

Assign a worksheet visibility macro to each option button

Use separate VBA macros to show one worksheet group and hide the other whenever an option button is selected.

1
Open the VBA editor

In Excel, open the Developer tab and select Visual Basic. If Developer is not visible, enable it in Excel's ribbon settings.

2
Add the macros

In the VBA editor, select the workbook project and choose Insert > Module. Paste the following code, replacing sheet_1 through sheet_6 with the exact worksheet names: Sub ShowGroup1() ThisWorkbook.Worksheets("sheet_1").Visible = True ThisWorkbook.Worksheets("sheet_2").Visible = True ThisWorkbook.Worksheets("sheet_3").Visible = True ThisWorkbook.Worksheets("sheet_4").Visible = False ThisWorkbook.Worksheets("sheet_5").Visible = False ThisWorkbook.Worksheets("sheet_6").Visible = False End Sub Sub ShowGroup2() ThisWorkbook.Worksheets("sheet_4").Visible = True ThisWorkbook.Worksheets("sheet_5").Visible = True ThisWorkbook.Worksheets("sheet_6").Visible = True ThisWorkbook.Worksheets("sheet_1").Visible = False ThisWorkbook.Worksheets("sheet_2").Visible = False ThisWorkbook.Worksheets("sheet_3").Visible = False End Sub

3
Assign each macro

Return to Excel, right-click the first form option button, and select Assign Macro. Choose ShowGroup1 and select OK. Repeat for the second button, assigning ShowGroup2.

4
Test both options

Click each option button and check the sheet tabs to confirm that its group is visible and the other group is hidden. Save the .xlsm workbook.

Assign a worksheet visibility macro to each option button
Keep a worksheet visible: Excel cannot hide every worksheet. Each macro shows its target group before hiding the other group so that a worksheet remains visible during the change.
Free Microsoft Office alternative

Manage Workbooks with WPS Spreadsheets

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Microsoft Office format support for an easier move between apps. VBA and form-control behavior may differ, so verify macro compatibility before relying on the Excel solution in WPS.

View and manage worksheet tabs in WPS SpreadsheetsOpen and edit Microsoft Excel formats such as .xlsx and .xlsNavigate workbooks with a familiar spreadsheet interfaceUse a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel report an error when hiding a worksheet?

Excel requires at least one worksheet to remain visible. Show the target group before hiding the other group, and check that every worksheet name in the VBA code matches its tab name.

How do I make the two option buttons mutually exclusive?

Place the form option buttons in the same group on the worksheet. If you use a group box, place both buttons inside it so selecting one clears the other.

Why can't I assign a macro to my button?

Right-click a Form Control option button and select Assign Macro. The macros must be public procedures in a standard module; use Insert > Module in the VBA editor to add them.

Will the worksheet visibility macros run in Excel for the web?

Excel for the web cannot run VBA macros. Open the .xlsm workbook in a desktop version of Excel that supports VBA to use the buttons.

How can I show a hidden worksheet manually?

In desktop Excel, right-click a visible sheet tab, select Unhide, choose the worksheet, and select OK.