Adding macro to reset selection

It is possible to add a macro to my dashboard that will do the following:

For cell B2 select England and for cell E2 select Please Select.

I would like to add this to a form control box that will essentially reset the dropdown selections to these values.

Thanks in advance

I am new, this forum has been a lifesaver.

Parhaps this?

Please Login or Register  to view this content.

BSB

Thanks this worked perfect.

Would you happen to know why when I select East of England and Yorkshire and Humber GETPIVOTDATA is displaying #REF!?

Thanks

EoE and Y&H in your pivot table have trailing spaces after them. This means that Excel cannot find them in the pivot data. "East of England " (with trailing space) is different to "East of England" (without trailing space).
Fix that issue at the source and all should work as planned.

BSB

Amazing this is great.

One last thing before I close the thread. I want to create a table using the same dropdown options. Is it possible to enable multi selection? The dashboard only has one which is perfect but would like the option of selecting more than one in a table format.

Thanks

Not sure what you mean by "create a table" in this instance. A pivot table?

You can apply filters to those so you can select only certain areas for example. Is that what you mean?

BSB

Apologies I am probably not being very clear.

In the attached excel tab "table" I have used the same drop down options. In this table format is there any way I can add a macro to allow more than one selection to populate the columns of the table respectively?

Thanks

Hmmmm.... Maybe. I'd need to think about it for a while.

My first question would be what would the results look like? If summed together for say two areas selected the numbers would work but the percentages wouldn't if you summed them together.

Perhaps using some hidden helper columns/tables it could be done....

BSB

If it was possible to add an additional column per selected area from the drop down. No to sum values but just show them as an additional column to mimic what a slicer would do in a pivot table?

Thanks

What about something like this?

I've added another button "Show Data" that will run the 2nd of the two subs below. It could probably be written better but I think it does what you need as it is.
It will add a new column to the table with the selected region/area's figures.

I've then slighty amended your ResetTable sub so it will delete any extra columns and clear the table back to empty.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Give it a try and see if it does what you need.

BSB