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.

Excel Pop Up Selector Tool Demo

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.

Excel Pop Up Selector Tool

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.

popupselector09

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.

popupselector10

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

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.

______________

Save

Save

Save

You may also like...

8 Responses

  1. Jeff Weir says:

    Weird – the macro won’t run for me in the sample file, and when I try to save the file Excel crashes. Using Excel 365.

    • Jeff, maybe the file was corrupted during the download, or something is conflicting with it. There’s nothing unusual in the macros — they just show and hide the shapes, or create a message box.

  2. Peter Buyze says:

    Hey Debra, I shared this on G+, you can find it here https://plus.google.com/+PeterBuyze/posts/ASCDu4mBRDi.

  3. Hugo says:

    Thx for showing the approach.
    Quite unclear to me:
    In procedure
    Sub ActionSelect()
    this line
    Set rng = [rng.Select]
    looks strange to me.
    rng will be set by a method of rng, that’s Nothing before first use?
    How does this statement work?

  4. Hugo says:

    Sorry.
    I’ve just seen, that rng.Select is not a method of range rng, but a named range, that’s updated in Worksheet_PivotTableUpdate().
    Confusing.

  1. July 21, 2017
  2. August 3, 2017

    […] might have seen AlexJ’s Pop Up Slicers workbook, a couple of weeks ago – it had one Slicer, grouped with macro buttons. This week’s […]

Leave a Reply to Debra Dalgleish Cancel reply

Your email address will not be published. Required fields are marked *