Forum Discussion
Drop down choices for users which lead to visiting each of 6 sheets, locked from other sheets
I'm trying to design a drop down choice box with, in each case, two choices of length, height and configuration, which users choose which then leads them to a results sheet (one of six) to provide certain figures. It would have to lock them out of the other sheets while they have activated their open sheet, to avoid users becoming confused. The open sheet would then have unprotected cells for them to put in prices, and protected cells for descriptions and calculations.
Is this possible, or am I asking too much of Excel?
If possible, what is the best method please?
Sachiel
It is possible to create a drop-down choice box in Excel that leads users to different sheets based on their selection. This can be achieved using Excel's data validation feature and macros.
Here's a general approach you can follow:
- Create a drop-down list in a cell using data validation. This list will contain the choices for length, height, and configuration.
- Assign a macro to the drop-down list that runs when a selection is made. The macro will navigate to the corresponding sheet based on the selected choice.
- Protect the other sheets to prevent users from accessing them while they are working on their chosen sheet.
- Unlock specific cells on the selected sheet to allow users to input prices, while protecting other cells that contain formulas or calculations.
This approach can be implemented in various versions of Excel, including Excel 2010, Excel 2013, Excel 2016, Excel 2019, and Excel for Microsoft 365. The specific steps may vary slightly depending on the version you are using, but the general concept remains the same.
To implement this solution, you will need to use VBA (Visual Basic for Applications) macros. If you are not familiar with VBA, you may need to learn the basics of VBA programming to create the necessary macros.
Alternatively, you can explore using Excel add-ins or custom form solutions to achieve a similar result with a more user-friendly interface.
Overall, while this task may require some advanced Excel skills and VBA programming, it is certainly possible to achieve the desired functionality in Excel.
