Home > Macros > Basics > Slicers Excel Popup Slicer Selector Slicers are great for showing a list of items, but they take up valuable space on your worksheet. Save space with this Excel popup Slicer selector -- the Slicer is hidden until you click the small button on the sheet, and disappears again, after you use it. Thanks to AlexJ, who shared this technique that he uses in his Excel files.

Video: Pop Up Slicer Selector Save worksheet space with this Excel popup Slicer selector. Click the button to make the selector appear, select items from the list, then click to show a message or image. This video shows how the pop up selector works, and walks you through the steps to build one in your own workbook. The written steps are below the video, along with the the full written transcript for this video. Video Timeline 00:00 Pop Up Slicer Demo 02:07 Excel Table and Pivot Table 02:42 Create New Workbook 03:15 Make Table for Slicer Items 04:20 Add Formula to Table 04:43 Make Pivot Table 05:13 Add Slicer for Pivot Table 05:55 Add 1st Button to Slicer 07:22 Add Worksheet Button 07:42 Add 2nd Button to Slicer 08:20 Add Macros to Workbook 10:37 Name Buttons and Pivot Table 11:24 Add Picture to Page 12:00 Assign Macros to Buttons 12:39 Group Buttons and Slicer 13:28 Add Code to Worksheet 14:37 Test Pop Up Slicer 15:20 Get the Sample File Video Transcript If you'd like to read the video transcript, click on the green check box below. When you're finished, you can click the check box again, to hide the transcript    ✅ Click here for Transcript Excel Popup Slicer Selector In Excel slicers are a great way to choose items from a pivot table or an Excel table. Pop Up Slicer Demo An example here, if I click 200 it shows up in this pivot table I can press control and select multiple items, and they appear here, But slicers do take up quite a bit of space on the worksheet. And if space is tight, that might not be the best option for you, so I'm going to show you a way that you can show slicers only when you need them, and the rest of the time they're hidden I'm going to delete this one, and show you what we've got, in this sample workbook that you can download from my website. There's just a little button here. It says Select Display. If I click that, the Selector appears. It's got a slicer in it, and a couple of buttons. It's connected to that same pivot table. If I click on something, you see it appearing over here. I'll ctrl + click to select a couple of items, and if I click this select display button, it shows a message with those two items listed. This is an example, a very simple example of what you could do. It's just listing the items. You could change the macro, so that it prints those sheets, or hides those, or something else. I'll click OK. The selector has disappeared. We just have our worksheet button again, so we're only using a very tiny bit of space here. There's one other thing that happens with that slicer. If I show it again, if I only select this one, called 300 Special, and now click Select Display, it shows this image And if I click away from that, there's worksheet code that will hide that image, when I change the selected cell on the worksheet Excel Table and Pivot Table So I'm going to show you how you can build this. It's based on an Excel table and here's the name of that table. We have sheet names, the order in which we'd like to see those names on the slicer, and in here, a formula that combines this order number, and the sheet name From this table, we've created a pivot table. And this pivot table has a slicer connected to it. Create New Workbook So I'm going to start a new workbook, and show you how to build this in your own file. So here's my brand new empty excel file I've named it excel pop-up slicer selector and saved it as macro enabled, so xlsm format because we're putting code in here, we have to save it in macro-enabled, either xlsm or XLS B otherwise Excel will just strip out all of the code when you close the workbook Make Table for Slicer Items The first thing we're going to do is create a little table that has the list of sheets or whatever else it is you want to appear in the slicer So I'll type the headings here - sheet name, order, and selector I will just create a list of regions, perhaps, for this one. I'm going to put these in a particular order. If I don't do that, they'll appear in alphabetical order on the slicer So I would like them in the order that I've got them listed here - one, two and three And I'll also put a Special item here, so we can show that picture, if we want to In here, we're going to be adding a formula, but first I'm going to change this to a named Excel table So I've selected a cell, on the Home tab, format as table, it has headers, click OK. And there we have our three columns Add Formula to Table So in here I'm going to write my formula - equals order and a space character and the sheet name And now when I press ENTER it's going to fill down automatically, because this is in a table. So that's the order they will appear in our selector Make Pivot Table Next we'll create a pivot table from this table. So Insert, Pivot table, and that is named table one I want it on an existing sheet. I'll put it right over here All I want in here is the sheet name, so now our macro can run through this list of sheet names and make something happen I'm also going to hide the grand total. So I'll right click, remove grand total Add Slicer for Pivot Table And we want to slicer based on this pivot table So with a cell selected there, Insert, Slicer We are showing the sheet names here, and in our slicer, we want this selector item, which is the number and sheet name, and click OK So here we have our slicer. You can leave it in this format, or choose one of the others. I'm going to make it wider, and change it to show two columns Add 1st Button to Slicer So we have our four items here, and I've left a bit of space at the bottom. We're going to be putting some buttons down here To add the buttons, we're just going to use shapes. So I'll Insert, shapes, and I'll use a rounded rectangle I would like a little shape here. That's going to be the button to click, to make something happen. And then the other corner we'll have our cancel button Move this down a little bit And then we'll have another button on the worksheet. So for this button, I'm going to be typing a label that says Select Display So we've got white text, and you can see it's running down the sheet here. So I'm going to change the settings of that I've got this shape selected, so I can go to the format tab here And in the size section, click that little pop-up button. Text box is what we want to work on. We want to allow that text to overflow the shape And down here, we don't want it to wrap, we want it to go out to the side. The left margin, I don't want it inside the button, I want it outside. So I'm going to increase the margin a bit,until I don't see the text on that shape anymore Select all of it, and make it black. Add Worksheet Button So there's our first button, and I'm going to copy that, because I want the same button on the worksheet. So ctrl C to copy, paste it up here with Ctrl V So that's our worksheet button Add 2nd Button to Slicer I'll move this down just a bit, and we want another button on the other side I'm going to copy this one, paste it over here. I want this button about here. This one, I'll make a different color. It will be our cancel button, I'll make it grey But I'm going to change the text here, to say Cancel And then, instead of increasing the left margin, I'll make that zero And now, I'll increase the right margin, so that cancel is to the left of this button We're going to refer to these buttons by name in the code, so we have to use specific names for them. We'll be changing those in a minute, and we're also going to get some macros into our workbook, and assign a macro to each of the buttons Add Macros to Workbook To get the code, you can use the code that's on my website, and there should be a link to that below. There's also a file that you can download. The demo that I showed at the beginning of this video, and it has the code in that I'm going to open up that download file again, and just copy the code out of there. If I go to Developer, Visual Basic, I can now see the download file here, AlexJ pop up selector. AlexJ created this technique and shared it on my website Now it has a module in it. If we double click there, go up to the top, we can see some global constants. These are the names of some of the things we're going to have: a picture called pics hidden our pivot table  - we're going to name pvt.select this name rng.select is the body of the pivot table, and it will be named automatically We have another bit of code that does that. We're going to be grouping some shapes, and called them grouped.msg , for message And here in this macro, SelectControl, we can see the names that we have to give the shapes . The worksheet shape button will be ctl.showselector I'm going to copy that We'll also call the buttons on the left of the slicer ctl.action and the other is ctl.cancel To get this code into our workbook very quickly, we just have to copy this whole module. So I'm going to select it. And here is my workbook where I don't have any code yet. I'll drag it down onto that. It puts a module folder, and that module Name Buttons and Pivot Table I'm going to come back to my worksheet, and name things Now for this button, I'm going to use the name that I copied from the macro. So ctl.showselector This button will be ctl dot action, press ENTER And this one will be ctl.cancel, press Enter We also have to name this pivot table, if it's referred to in the code as pvt.select Go to the Analyze tab, and change its name, press Enter Add Picture to Page We also have to put a picture onto this page. It's going to be hidden most of the time. I'm going to copy the one out of the demo file, but you could insert your own picture So here's the demo file. I'm going to show that picture by selecting it, click on it, and Ctrl C, to copy. I'll go back to my file and Ctrl V to paste it here on the sheet I'll check the name. It is called pic.hidden, so if you've pasted in your own picture, make sure to use that name I'll click away, so nothing is selected right now Assign Macros to Buttons Now that we've named our buttons, I'm going to assign macros to each of them So right click, and if you see this, you've just moved it a little bit. Just try again. Right click, Assign Macro, and each of these will be select control, click OK Same thing here, right click, assign macro, select control and OK Right click, assign macro, select control, OK Each button now, if you point to it, shows that hand, that will run the macro, when you click Group Buttons and Slicer We're going to group these three shapes, the slicer and the two buttons So click somewhere on the slicer, in an empty spot, to select that then ctrl and click on each of the buttons, so all three shapes are selected Right click somewhere on the slicer, Group, and Group So now you can see the border around all three, but we can still point to these buttons and the hand appears This shape is a group and it got a default name, group6, and we want this to be called group.msg. It's going to be our messaging system here Press enter, and that's the name we used in the code. Add Code to Worksheet The next step will be to add some code to this worksheet, so that something will happen if we click a cell. It will hide our selector or our image, and it will also look at our pivot table if it's been updated. I'm going to right click here, and view code. All that we have here is option explicit, so I'm just going to get rid of that Go to my demo file, and here on the demo sheet, I'll double click And there's the code from the sample file. Going to select all of that, copy it with ctrl C. Come to my workbook, sheet1 and ctrl V, to paste it So here's the code that runs when the pivot table is updated, by clicking the slicer. It creates a range from the data body range of the pivot table, and creates a named range from that And also, when you change the selected cell, it hides our picture, and it hides that pop-up selector. Test Pop Up Slicer Minimize that, and click on a cell, and it hid the things that were visible So now, when I click, there's our selector, and it shows our items and two buttons I'm going to select West,  and ctrl click and select Central, and those two items are here. And in our pivot table, if I select again, and only select 4 Special, now it shows our image. And when I click away from it, it's hidden. So we have a very small button on our worksheet, that lets us open our slicer make choices. And then something will happen, based on our selections.

Use the Pop Up Selector

Here's how to use the pop up selector in the sample workbook that you can download below.

• Click on the small worksheet button, to open the pop up selector.

• Click on one or more items in the list, to select them.
• Click the Select Display button, to take action on the selected items

• The pop up selector disappears, and a message box shows a list of the selected items.
• Click OK to close the message box.

When you're selecting items, if you select the 300 Special item, and no other items, something different happens.

Instead of a message box, an image appears. (Happy 150th birthday, Canada!) The image disappears when you click any cell on the worksheet.

Pop Up Selector Main Steps

The pop up selector is an Excel Slicer that is grouped with other shapes.

To build the pop up selector, you'll follow these main steps -- the details are in the sections below, and see the step-by-step intructions in the video..

1. Build an Excel Table, Pivot Table and Slicer
2. Add Buttons on Top of the Slicer
3. Add Macros for the Buttons
4. Group the Slicer and Buttons
5. Add a Worksheet Button and Macro
6. Add Worksheet Code

Build Table, Pivot Table, Slicer

The main component of the popup selector is the Excel Slicer. It is based on an pivot table, which shows the fields from a simple Excel Table.

NOTE: If you create a workbook to try this technique, be sure to save the file as macro-enabled (xlsm or xlsb format). Otherwise, Excel will automatically remove all the code when you close the workbook.

Excel Table

In your workbook, create an Excel Table, with a list of items for the pop up selector. In the sample workbook, the table has three columns:

1. SheetName -- name of worksheet
2. Order -- sort order for the Slicer list
3. Selector -- formula to combine list order and sheet name

NOTE: If you want the Slicer items to appear in alphabetical order, you don't need the Order or Selector columns.

Pivot Table

Next, create a Pivot Table, based on the Excel table. Then, add the SheetName field to the Rows area -- these are the sheet names that will be used in the macro

Slicer

Then, insert a Slicer, to show the items in the pivot table's Selector field.

In the Slicer Settings, change the Caption to "Select Display", or other text that will help people use the Slicer

Add Buttons to Slicer and Sheet

Next, buttons will be added to the Slicer and the worksheet.

Slicer Buttons

• Make the Slicer taller, so there is about 1/2" (1 cm) of empty space below the item buttons.
• In the left bottom corner, draw a small round shape, and format it to look like a button.
  • Add a text label on that shape, such as "Select Display"
  • Adjust the round shape's Text Box settings to show the text off to the right side of the button.
• Add another round shape in the bottom right corner, with the text label "Cancel"

Worksheet Button

To create a button on the worksheet, that will open the pop up selector, follow these steps:

• Copy the button at the bottom left of the Slicer
• Paste the button onto the worksheet, just above the top left corner of the slicer.
• Leave the text label as "Select Display", or change to different text, if you wish.

Name the Buttons

You will be adding macros that check the button names, so follow these steps to name the 3 buttons:

• Click on the worksheet button, to select it
• Click in the Name Box, to the left of the Formula Bar
• Delete the default name
• Type ctl.ShowSelector then press Enter

Repeat those steps to name the 2 Slicer buttons, using the following names:

• Cancel button -- ctl.Cancel
• Select Display button -- ctl.Action

Add Macros for Buttons

Next, you'll add macro code to your workbook, and then assign macros to the buttons on the Slicer.

To add the code, insert a new module in your workbook, and paste in the following code. There are three macros in the module:

1. SelectControl: Calls one of the other macros, based on which shape was clicked (Application.Caller)
2. ShowDialog: Shows or hides the group of shapes
3. ActionSelect: Hides the group of shapes, and displays the message box or the special image.

The SelectControl macro will be assigned to the buttons, in the next step.

Option Explicit
Global Const strGrp As String = "Group.Msg"
Global Const strPT As String = "pvt.Select"
Global Const strPix As String = "pix.Canada"
Global Const strRng As String = "rng.Select"

'=================================

Sub SelectControl()

Dim ctlID As String

On Error GoTo Err01
ctlID = Application.Caller

Select Case ctlID
  Case "ctl.ShowSelector": _
    Call ShowDialog("Show", ctlID)
  Case "ctl.Action": _
    Call ActionSelect
  Case "ctl.Cancel": _
    Call ShowDialog("Hide", ctlID)
  Case Else: ' Stop
End Select

XIT:
  Exit Sub

Err01:
  Resume XIT
  Resume
End Sub

'=================================

Sub ActionSelect()
  Dim ws As Worksheet
  Dim rng As Range
  Dim sh As Shape
  Dim i As Long
  Dim msg As String
  Const msgSuffix As String _
      = vbLf & vbTab
  Const msg0 As String _
      = "Selected Items: " & msgSuffix
  Application.EnableEvents = False

  Call ShowDialog("Hide")
  Set ws = ActiveSheet
  Set rng = [rng.Select]
  Set sh = ws.Shapes(strPix)
  
  ' For demo - create list of selected
  '    sheets and display in a message
  For i = 1 To rng.Cells.Count
    If i = 1 Then
      msg = rng.Cells(i)
    Else
      msg = msg & ", " _
        & vbLf & vbTab & rng.Cells(i)
    End If
  Next i

  ' For demo - show special shape
  '    if only "Special" is selected
  If msg = "Special" Then
    sh.Visible = True
  Else
    sh.Visible = False
    msg = msg0 & msg
    MsgBox msg
  End If

  Application.EnableEvents = True
  Set sh = Nothing
  Set rng = Nothing
  Set ws = Nothing

End Sub

'=================================

Sub ShowDialog(Optional uAction _
    As String = "Hide", _
    Optional IDcaller As String = "")
  ' shows or hides pop up selector
  ' postions it over calling button
  Dim ws As Worksheet
  Dim si As SlicerItem
  Dim sh As Shape
  Dim shC As Shape
  Dim pt As PivotTable
  Application.EnableEvents = False

  On Error GoTo Err01

  Application.ScreenUpdating = False
  Set ws = ActiveSheet
  Set sh = ws.Shapes(strGrp)
  Set pt = ws.PivotTables(strPT)

  If uAction = "Show" Then
    pt.PivotCache.Refresh
    With sh
      .Visible = True
      If IDcaller <> "" Then
        Set shC = ws.Shapes(IDcaller)
        .Top = shC.Top
        .Left = shC.Left
        .ZOrder msoBringToFront
      End If
    End With
  Else
    sh.Visible = False
  End If

XIT:
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  Set sh = Nothing
  Set si = Nothing
  Set pt = Nothing
  Set ws = Nothing
  Exit Sub

Err01:
  Resume XIT
  Resume
End Sub

'=================================

Assign Macros to Buttons

Now that the macro code is in the workbook, follow the steps below, to assign the SelectControl macro to each button.

The SelectControl macro identifies which button was clicked (Application.Caller), then calls one of the other macros -- ShowDialog or ActionSelect.

To assign a macro:

• Right-click one of the buttons, and click Assign Macro
• Click the SelectControl macro in the list
• Click OK

Do this for all 3 of the buttons -- the 2 on the Slicer, and the 1 on the worksheet.

Group Slicer and Buttons

To create the Pop Up Selector, follow these steps to created a grouped shape:

• Click on an empty part of the Slicer, to select it
• Press the Ctrl key, and click on each of the buttons, to select them
• Right-click on an empty part of the Slicer, and point to the Group command, then click Group
• To name the grouped shape:
  • Click in the Name Box, to the left of the Formula Bar
  • Delete the default name
  • Type Group.Msg then press Enter
• Click on the worksheet, to unselect the grouped shape.

Add an Image

If only the "300 Special" item is selected in the Slicer, a hidden image will appear. To add an image to your worksheet:

• Copy the image from the pop up selector sample workbook, and paste it onto your worksheet.
  • OR, insert a different picture, from your own computer
• Position the picture on the worksheet, where you'd like it to appear
• With the picture selected:
  • Click in the Name Box
  • Type the name pix.Hidden
  • Press Enter

Add Worksheet Code

To make the pop up selector work, there are worksheet events that run when you do one of the following actions:

• 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

Copy the code below onto the worksheet code module.

Private Sub Worksheet_PivotTableUpdate _
  (ByVal Target As PivotTable)
Dim rng As Range

If Target.Name = strPT Then
  Set rng = Target.DataBodyRange
  ThisWorkbook.Names.Add _
    strRng, RefersTo:=rng
  Set rng = Nothing
End If

End Sub

'=================================

Private Sub Worksheet_SelectionChange _
  (ByVal Target As Range)

Me.Shapes(strPix).Visible = False
Call ShowDialog("Hide")

End Sub

Test the Pop Up Selector

To test the Pop Up Selector:

• Click the worksheet button (ctl.ShowSelector)
• The SelectControl macro runs, and checks the name of the button that called it
• Because the ctl.ShowSelector button was clicked, the ShowDialog macro runs, with the "Show" option. It updates the pivot table, and shows the pop up selector.

In the Slicer, click one of the items.

• That updates the pivot table, and triggers the pivot table update code.
• The named range, rng.Select is rebuilt, based on the pivot table's data body range.

Press the Ctrl key, and click on another item

• The second item appears in the pivot table
• The pivot table update code runs, and rebuilds the named range, rng.Select

Click the Select Display button (ctl.Action)

• The SelectControl macro runs, and checks the name of the button that called it
• Because the ctl.Action button was clicked, the ActionSelect macro runs, which:
  • Creates a list of the selected items
  • Shows a message with those names
  • Hides the pop up selector
• NOTE: If only the 300 Special item was selected, it shows the hidden image named pix.Canada

To see the hidden image:

• Click the worksheet button to open the pop up selector
• Click on 300 Special , to select only that item
• Click the Select Display button, and the hidden image appears

Click on a worksheet cell -- the Worksheet_SelectionChange code runs, and hides the image.

Other Actions

The pop up selector in the sample workbook shows the selected items in a message box.

You can put different code in the ActionSelect macro, and adapt it for your Excel workbooks. For example:

• Print all the selected sheets
• Show or hide selected sheets
• Send emails to selected names

Pop Up Selector Tips

Here are a few things to keep in mind, when you use grouped shapes and/or Slicers.

• A Slicer can't be copied into another workbook, and reconnected to a different pivot table – you have to build a new Slicer in the new workbook
• If you Ungroup the  shapes, and then regroup them, the group gets a different name. Be sure to restore the original name, or the macro won't recognize the grouped shape.
• To modify the shapes, use the Selection Pane (Home tab of the Excel Ribbon) to show the shapes, if the macros have hidden them

To make it easy to create a new pop up selector:

• Make a template from the original file (Save as a macro-enabled template).
• Then, create a new workbook from your template, and add your data and other components into that file.

Get the Sample File

To see how the pop up selector works, download the Pop Up Selector sample workbook. The zipped file is in xlsm format, and contains macros. The Slicers will work in Excel 2007 and later versions.

Find more of AlexJ's sample files on his page: Sample Excel Files by AlexJ

Related Links

Here are a few other ways to select items from a list, and use those items in a macro.

Show Specific Excel Sheets

Show Sheets by Tab Color

Create a Worksheet List Box

Create a Popup Listbox (UserForm)