List Worksheet Scenarios in drop-down via VBA

[fournit]

I have a rather large financial model that relies on a substantial number of scenarios across multiple worksheets. My goal is to bypass the actual use of the Scenario Manager and control scenario selections from dropdown in several dashboards. I'm new to VBA, however, I've successfully written the code to trigger scenarios from multiple worksheets from drop-downs in a single dashboard. The problem is that the drop-down are driven by tables in which I have to manually maintain once I've created a new scenario. What I'd like to do is programmatically populate the drop-downs by reading the scenarios for a given worksheet with VBA code. That way, I only have to create the scenario(s) and then the dashboard drop-downs will automatically populate with the worksheets scenarios. I've scoured the forums and Microsoft's VBA information and have not found a way to accomplish this.

My drop-downs are currently using a Data Validation lists tied to a dynamic list going against the manually populated tables. When the value in the drop down changes, my code reads the value of the drop down into a variable and then uses the Worksheet.Scenarios.Show method to trigger the scenario.

[Roel Jongman]

Hi fournit,

I found some code online and changed that for use in a table. for sure you have to adjust to your workbook specifics like worksheetname and tablename etc

Please Login or Register  to view this content.

the code deletes the databodyrange of the table each time it is used and rebuilds the list. this is the most practical way in VBA that makes sure the list is always accurate and no old scenarios that may have been deleted stay in the list.

See attachement to test and play with the code.

[fournit]

Much appreciated Roel and thank you for the quick response! I will work with your sample code today and see if I can work my way through it. I'll let you know if I have any questions.

Regards,
Tom