Change Multiple Cells at Once in Excel - Save and Switch Excel Input Scenario

[@Up4Excel]

👉 Up4Excel Downloads Library: »» ml.up4excel.com/library ✅ FREE Access to ALL Up4Excel Files. Includes Excel Templates, Training Workbooks, Example Data, Cheat Sheets and more. New Content Added Weekly!

[@Up4Excel]

📗 Get Your Video Workbook Template Here: »» cutt.ly/V2011MFD 🎯 Change an entire input set for your spreadsheet model from a single dropdown list. How much time would YOU save if all your spreadsheet inputs were scenarios you pick on a dropdown list? This tutorial reveals the secret!

[@wayneedmondson1065]

Hi John. Interesting application. If you don't want to use an activeX control, you can do it with a standard Validation Dropdown list and a small amount of VBA. Create a table of the names of your scenarios. Pick a cell for the Validation List dropdown and point the Source to the table (handy if you want to add new scenarios). Name the validation cell "Choice". Go to the VBE and add the following code for the worksheet: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("Choice")) Is Nothing Then ActiveSheet.Scenarios(Range("Choice").Value).Show End If End Sub When you make a selection from the dropdown, the worksheet_change event will show the scenario in the named range "Choice" which is whatever was selected on the validation list. This is just another way to get there. For a variety of reasons, I tend to avoid using activeX controls. If I can get it done with a form control and or standard ribbon available features, I go there first. Hope someone finds this useful. Thanks for the tips and inspiration to learn new things :)) Thumbs up!!

[@Up4Excel]

Thanks for the alternative way of getting there Wayne....I'm sure many people will find that useful 👍 I guess you could also combine this with a bit of my code to update the table with any new scenarios added too and make it fully dynamic. Out of interest, why do you try to avoid ActiveX controls?

[@wayneedmondson1065]

@@Up4Excel Hi John. They can be quirky at times. Also, coding them and having to use design mode when working with them on the worksheet is a bit clunky compared to standard coding in the VBE and standard objects on the worksheet. I use them sometimes, but generally try to solve the problem first with form controls and worksheet events and or standard code. Maybe just my preference vs. anything being inherently wrong with ActiveX.

[@Up4Excel]

@@wayneedmondson1065 You're right they are a bit clunky and quirky - some of the properties sometimes don't do exactly what they should do for example. I like Form Controls too if I can get away with them...they still look the business but are really straight forward.