Home > Macros > Basics > Slicers Excel Popup Slicer SelectorSlicers 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 SelectorSave 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
Video TranscriptIf 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 Excel Popup Slicer SelectorIn Excel slicers are a great way to choose items from a pivot table or an Excel table. Pop Up Slicer DemoAn 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.
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.
Excel Table and Pivot TableSo 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 WorkbookSo 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 ItemsThe 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
In here, we're going to be adding a formula, but first I'm going to change this to a named Excel table
And there we have our three columns Add Formula to TableSo 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 TableNext 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 TableAnd we want to slicer based on this pivot table
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 SlicerSo 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.
And then we'll have another button on the worksheet.
Add Worksheet ButtonSo there's our first button, and I'm going to copy that, because I want the same button on the worksheet.
So that's our worksheet button Add 2nd Button to SlicerI'll move this down just a bit, and we want another button on the other side
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 WorkbookTo 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
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:
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 .
I'm going to copy that
To get this code into our workbook very quickly, we just have to copy this whole module.
Name Buttons and Pivot TableI'm going to come back to my worksheet, and name things
We also have to name this pivot table, if it's referred to in the code as pvt.select
Add Picture to PageWe also have to put a picture onto this page. It's going to be hidden most of the time.
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 ButtonsNow that we've named our buttons, I'm going to assign macros to each of them
Each button now, if you point to it, shows that hand, that will run the macro, when you click Group Buttons and SlicerWe're going to group these three shapes, the slicer and the two buttons
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 WorksheetThe 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.
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 SlicerMinimize that, and click on a cell, and it hid the things that were visible
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. |
Here's how to use the pop up selector in the sample workbook that you can download below.
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.
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..
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.
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:
NOTE: If you want the Slicer items to appear in alphabetical order, you don't need the Order or Selector columns.
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
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
Next, buttons will be added to the Slicer and the worksheet.
To create a button on the worksheet, that will open the pop up selector, follow these steps:
You will be adding macros that check the button names, so follow these steps to name the 3 buttons:
Repeat those steps to name the 2 Slicer buttons, using the following names:
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:
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 '=================================
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:
Do this for all 3 of the buttons -- the 2 on the Slicer, and the 1 on the worksheet.
To create the Pop Up Selector, follow these steps to created a grouped shape:
If only the "300 Special" item is selected in the Slicer, a hidden image will appear. To add an image to your worksheet:
To make the pop up selector work, there are worksheet events that run when you do one of the following actions:
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
To test the Pop Up Selector:
In the Slicer, click one of the items.
Press the Ctrl key, and click on another item
Click the Select Display button (ctl.Action)
To see the hidden image:
Click on a worksheet cell -- the Worksheet_SelectionChange code runs, and hides the image.
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:
Here are a few things to keep in mind, when you use grouped shapes and/or Slicers.
To make it easy to create a new pop up selector:
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
Here are a few other ways to select items from a list, and use those items in a macro.
Last updated: March 3, 2023 12:46 PM