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.
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. Thanks to AlexJ, who contributed this technique.
This animated screen shot shows how the pop up selector works, and the written instructions are below.
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..
This video shows how the pop up selector works, and walks you through the steps to build one in your own workbook.
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: November 29, 2018 3:14 PM
Contextures RSS Feed