Excel Pop Up Selector Tool With Slicer
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
In this animated screen shot, you can see how the pop up selector works. Click the worksheet 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 more complicated with the selected items.
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
- Shapes are added in that space, 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 sample workbook, AlexJ has an Excel Table, with a list of items required for the pop up selector. He numbered the items, so they would appear in a specific order in the Slicer.
There's also a Pivot Table, based on that Excel table -- 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.
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
How to Set up the Pop Up Selector
This video shows how the pop up selector works, and walks you through the steps to build one in your own workbook. For written details, and the sample file, go to the Excel Popup Slicer Selector page on my Contextures website.
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.
The zipped file is in xlsm format, and contains macros. The Slicers will work in Excel 2007 and later versions.