How To Create A Pop Up Table In Excel

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 video shows how the pop up selector works, and walks you through the steps to build one in your own workbook.

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..

Build an Excel Table, Pivot Table and Slicer
Add Buttons on Top of the Slicer
Add Macros for the Buttons
Group the Slicer and Buttons
Add a Worksheet Button and Macro
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:

SheetName -- name of worksheet
Order -- sort order for the Slicer list
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:

SelectControl: Calls one of the other macros, based on which shape was clicked (Application.Caller)
ShowDialog: Shows or hides the group of shapes
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.

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