Use an Excel Drop-Down List to Open Protected Result Sheets in WPS Spreadsheets
Question details
The user needs a controlled way to navigate from a drop-down list to the matching protected result worksheet in WPS Spreadsheets, while preventing edits to formulas and descriptions.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A workbook contains multiple result sheets and the user wants one dashboard drop-down that automatically opens the selected result sheet for viewers or operators.
- Observed behavior
- The intended state is that selecting a value in the drop-down activates the corresponding result sheet, but only price-entry cells are unlocked and all formula and description cells remain protected.
Before you start, save a copy of the workbook as a macro-enabled file and make sure the Developer tab is visible in WPS Spreadsheets. If existing sheet protection is active, unprotect the relevant sheets or workbook structure temporarily so you can modify locked cells and sheet visibility.
Create a Data Validation Drop-Down and Use a Worksheet_Change Macro to Open Protected Result Sheets
Use this VBA method when you want a single drop-down to navigate to the matching protected result sheet while keeping formula and description cells locked.
This method uses Data Validation for the visible list and a Worksheet_Change event for automatic navigation, so users do not need buttons or hyperlinks.
In WPS Spreadsheets, worksheet protection still runs normally while VBA activates the selected sheet; only cells explicitly unlocked before protection remain editable.
In WPS Spreadsheets, open your workbook and add or identify the result sheets, such as Result1, Result2, and Result3, plus a Dashboard sheet where the drop-down list will live.
Select the drop-down cell on the Dashboard, go to Data > Data Validation, choose List from the Allow box, and enter the result sheet names separated by commas or reference a range containing the names.
Press Alt+F11 or click Developer > Visual Basic, find the Dashboard sheet module in Project Explorer, and double-click it to open the code window.
Paste a Worksheet_Change procedure that checks whether the changed cell is the drop-down cell, such as Target.Address = "$C$4", and then runs Worksheets(Target.Value).Activate to open the selected result sheet.
On each result sheet, select the cells users should edit, right-click > Format Cells > Protection, clear Locked, then go to Review > Protect Sheet and set a password. Keep formula and description cells locked.
Right-click non-navigation sheet tabs and choose Hide, or use Review > Protect Workbook to protect the workbook structure so users cannot unhide or alter sheets.
Set Up Protected Sheet Navigation with WPS Spreadsheets
WPS Spreadsheets lets you create the Data Validation drop-down, add the Worksheet_Change VBA macro, and protect formula and description cells in one free Office suite.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, open the Excel workbook that contains the result sheets, and save it as a macro-enabled workbook if prompted.
- 2. Create the navigation drop-down: Select the dashboard cell, go to Data > Data Validation, choose List, and enter the result sheet names as the source.
- 3. Add the Worksheet_Change macro: Press Alt+F11 to open the Microsoft Visual Basic for Applications editor, open the dashboard sheet module, and insert the code that activates the selected worksheet.
- 4. Protect the result sheets: Unlock only the price-entry cells on each result sheet, then click Review > Protect Sheet to lock all formula and description cells.
Frequently Asked Questions
Can I use a drop-down list to switch sheets without VBA?
Yes, you can create a table of contents with hyperlinks or use the HYPERLINK function, but automatic navigation from a Data Validation choice requires VBA. Use Data Validation for the drop-down, then add a Worksheet_Change procedure to activate the matching sheet.
Why does the macro not run when I change the drop-down?
Check that macros are enabled and that the workbook is saved as a macro-enabled file. Also verify that the Worksheet_Change code is placed in the worksheet module for the sheet containing the drop-down cell, not in a standard module.
How do I keep formulas and descriptions safe after allowing sheet navigation?
Before protecting each result sheet, select the input cells, right-click Format Cells > Protection, and clear Locked. Then go to Review > Protect Sheet to lock the remaining formula and description cells.
Can I hide the result sheets but still let the drop-down open them?
You can hide result sheets, but the VBA code must unhide the target sheet before activating it. If you prefer simpler navigation, keep the result sheets visible and protect the workbook structure to prevent users from changing sheet visibility.
What should I do if the drop-down source list needs to come from a range?
Define a named range or use a hidden helper range containing the sheet names as the Data Validation source. This lets you update the drop-down list without changing the VBA code.
