Slicers make it easy to select from a list of items, but they take up valuable space on your worksheet. To get the benefits of a Slicer, without the real estate cost, AlexJ made this Excel Pop Up Selector – it’s hidden until you click the small button on the sheet, and disappears again, after you use it.
Excel Pop Up Selector Tool Demo
This animated screen shot shows how the pop up selector works. Click the button to make the selector appear, select items from the list, then click to show a message or image. This is a simple example – you could make the code do something fancier.
Pop Up Slicer Video
To see a longer version of how the Pop Up Slicer works, watch this video. There are more details below, and the sample file is on the Excel Popup Slicer Selector page on my Contextures website.
Pop Up Selector Setup
The pop up selector is an Excel Slicer that is grouped with other shapes. The Slicer is sized to leave empty space at the bottom, and that’s where the other shapes are added, formatted to look like buttons.
Those buttons have macros assigned, and there’s a bit of worksheet code too.
Pop Up Slicer Setup
In the workbook, AlexJ has an Excel Table, with a list of items required for the pop up selector.
There’s also a Pivot Table, based on that Excel table, and the Slicer shows the items in the pivot table’s Selector field.
The pivot table’s data body range is named when the pivot table updates, and that range is used to get the list of items.
Worksheet Events
The Demo sheet also has worksheet events that run when you:
- Select a cell (Worksheet_SelectionChange) — hides the special image, and calls the macro named “ShowDialog”, which shows the grouped Slicer shape
- Use the Slicer (Worksheet_PivotTableUpdate) — creates a named range, based on the pivot table’s data body range
Other Uses for Pop Up Selector
AlexJ created this pop up selector technique for his Excel files at work, so that people could make print selections.
To show us the basics of how it works, he created this simple example – instead of printing the selected items, it shows the selected items in a message box.
You can put your own twist on AlexJ’s technique, and adapt it for your Excel workbooks. What would you do with the Pop Up Selector?
- Print all the selected sheets
- Show or hide selected sheets
- Send emails to selected names
- Open selected Excel files?
- What else?
Get the Pop Up Selector Sample File
To get AlexJ’s Excel Pop Up Selector Tool, and to see all the details and tips on how to build it, go to the Excel Popup Slicer Selector page on my Contextures website.
You can get the sample file there too. The zipped file is in xlsm format, and contains macros. The Slicers will work in Excel 2007 and later versions.
______________
One thought on “Excel Pop Up Selector Tool With Slicer”