Contextures

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.

Introduction

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.

Excel Pop Up Selector Tool Demo

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.

Excel Pop Up Selector Tool Demo

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

worksheet button for popup

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

list of items

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

special item

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

hidden image appears

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

This video shows how the pop up selector works, and walks you through the steps to build one in your own workbook.

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.

Excel table for Slicer

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

Slicer settings

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"

button label text

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.

worksheet button

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.

assign macro to button

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.

empty space in Slicer

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.

Pop Up Selector 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)

Search Contextures Sites

 

Excel Tools Add-in

 

Free Pivot Table Tools

 

 

Peltier Tech Charts for Excel 3.0

 

 

 

Pivot Power Premium

 

Excel Data Entry Popup List

 

Excel UserForms for Data Entry

 

Last updated: August 1, 2017 4:06 PM
Contextures RSS Feed