Show and Hide Excel Worksheets with Option Buttons
Question details
Use two form option buttons to display different groups of Excel worksheets.

- 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.
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.
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.
In Excel, open the Developer tab and select Visual Basic. If Developer is not visible, enable it in Excel's ribbon settings.
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
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.
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.

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.

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.




