How To Filter Excel Data In A PowerPoint Presentation

Filter Microsoft Excel Data In A PowerPoint Presentation With VBA Macro Code

PowerPoint Filtering

This was a great idea that came from a reader named Chris Morgan. He wanted to know if it would be possible to create a PowerPoint slide that could filter Excel data. I had never attempted such a task but after reading his question, I immediately envisioned how it could be a useful and impressive functionality to have during a presentation. Here is my take on how to accomplish this using a Combo Box and a little VBA coding magic.

Filtering during Presentation Mode in Microsoft PowerPoint

Adding The Combo Box To Your Slide

In order to insert a combo box into your slide, you will need to access PowerPoint's Developer Tab. While on the Developer Tab, look for the Combo Box icon within the Controls group (highlighted below). Once the icon is clicked, you can simply draw the combo box on your desired PowerPoint slide with your mouse.

How To Insert A Combo Box In Microsoft PowerPoint

Loading Values Into The Combo Box

PowerPoint does not have access to Form controls; only ActiveX controls. One major benefit of Form controls is that you can connect the forms to values on a spreadsheet. Obviously since we are working in PowerPoint, we do not have access to spreadsheet-style data storage, hence there are no available Form controls.  With ActiveX controls, we must load in values to the combo box via VBA code. First we will want to capture an Event to trigger the code to run. The event I chose to use was the Got Focus event (meaning whenever the combo box is selected or has the focus on-screen, the code will execute). You will use this event trigger to run a subroutine that will populate the combo box with choices for the user to select from.

Private Sub ComboBox1_GotFocus()
'PURPOSE: Event to capture when a user selects the combo box
'SOURCE: www.TheSpreadsheetGuru.com

'Run Macro to populate combo box with values
  Call AddItemsToComboBox

End Sub

The AddItemsToComboBox subroutine loads in values to the combo box during the PowerPoint presentation.  In order to prevent the Combo Box from reloading the same values over and over again (saving time), you can include some logic to prevent it from adding new items if there is already data stored in the combo box.  Depending on your specific setup you may need to tweak the logic a little bit (ie if you have 10 items in your drop down list, you would want to use "<10").

Sub AddItemsToComboBox()
'PURPOSE: Populate a Combo Box with Values if not Already Populated
'SOURCE: www.TheSpreadsheetGuru.com

If Slide2.ComboBox1.ListCount < 5 Then
  Slide2.ComboBox1.ListRows = 5
  Slide2.ComboBox1.AddItem ("All")
  Slide2.ComboBox1.AddItem ("Bob")
  Slide2.ComboBox1.AddItem ("John")
  Slide2.ComboBox1.AddItem ("Ben")
  Slide2.ComboBox1.AddItem ("Sally")
End If

End Sub

Pulling In The Excel Data

Now for the last piece of code. The next thing to do is to retrieve the filtered data from the Excel source file. This code will be triggered by the Change event handler, as it will execute its code whenever the combo box's value is changed by the user. Essentially what the code does is

  1. Open up the Excel source file
  2. Filter the data on the user's selection from the combo box
  3. Replace the current image on the slide with a new "filtered" image
  4. Close the Excel file (without saving it)
  5. Resize image based on previous image's dimensions

I will not go into depth explaining how this subroutine works, but I tried to leave meaningful code comments to explain what each line does in the macro.  Leave a comment in the comments section if you would like an explanation for a certain part of this code and I would be more than happy to explain.

Private Sub ComboBox1_Change()
'PURPOSE: Bring Filtered Excel Data into PowerPoint Presentation
'SOURCE: www.TheSpreadsheetGuru.com

Dim wb As Object
Dim tbl As Object
Dim ExcelApp As Object
Dim sld As Slide
Dim ComboBx As Shape, NewShape As Shape, OldShape As Shape
Dim myCriteria As String, ExcelFilePath As String
Dim ComboBoxName As String, DataImageName As String
Dim ExcelTableName As String, TableSheet As String
Dim SlideNumber As Long

'Input Values
  ExcelFilePath = "C:\Users\chris\Desktop\Data.xlsm"
  SlideNumber = 2
  ComboBoxName = "ComboBox1"
  DataImageName = "SalesData"
  ExcelTableName = "Table1"
  TableSheet = "Sheet1"
  
'Store Object Variables
  Set sld = ActivePresentation.Slides(SlideNumber)
  Set ComboBx = sld.Shapes(ComboBoxName)

'Create an Instance of Excel
  On Error Resume Next
    
    'Is Excel already opened?
      Set ExcelApp = GetObject(class:="Excel.Application")
    
    'Clear the error between errors
      Err.Clear

    'If Excel is not already open then open PowerPoint
      If ExcelApp Is Nothing Then Set ExcelApp = CreateObject(class:="Excel.Application")
    
    'Handle if the Excel Application is not found
      If Err.Number = 429 Then
        MsgBox "Excel could not be found, aborting."
        Exit Sub
      End If

  On Error GoTo 0

'Open Excel File
   Set wb = ExcelApp.Workbooks.Open(ExcelFilePath)

'Determine User Selection
  myCriteria = ComboBx.OLEFormat.Object.Value

'Filter on select item
Set tbl = wb.Worksheets(TableSheet).ListObjects(ExcelTableName)

  If myCriteria = "All" Then
    tbl.Range.AutoFilter Field:=1
  Else
    tbl.Range.AutoFilter Field:=1, Criteria1:=myCriteria
  End If
  
'Copy/Paste Data
  tbl.Range.Copy

'Store characteristics about current Data Image & remove
  Set OldShape = sld.Shapes(DataImageName)
  x = OldShape.Left
  y = OldShape.Top
  Z = OldShape.Width
  OldShape.Delete
  
'Paste to PowerPoint and position
  sld.Shapes.PasteSpecial DataType:=ppPasteEnhancedMetafile
  
'Set Variable Equal To Newly Pasted Image
  'Handle Excel 2010 & prior bug where combobox stays "in front" of all images no matter what
    If sld.Shapes(sld.Shapes.Count).Type = msoOLEControlObject Then
      Set NewShape = sld.Shapes(sld.Shapes.Count - 1)
    Else
      Set NewShape = sld.Shapes(sld.Shapes.Count)
    End If
  
'Reposition and Resize Filtered Picture
  NewShape.Left = x
  NewShape.Top = y
  NewShape.Width = Z
  NewShape.Name = DataImageName
        
'Close Excel File
  ExcelApp.CutCopyMode = False
  wb.Close SaveChanges:=False
  
End Sub

Determining The Various Object Names

In order to help you tweak the code to your needs I have created a brief video to help show you how to setup both your PowerPoint and Excel files so they can work in unison.   In the video I mostly cover how to determine specific names and values needed in the VBA macro code that will most likely be different for you depending on your individual needs.

Possible PowerPoint Bug?

I would like to note that there seems to be a bug in PowerPoint that crawls through the cracks when executing this VBA code.  When you initially start your PowerPoint presentation and click on the combo box for the first time, your combo box list will look like this:

Filter Microsoft Excel Data In A PowerPoint Presentation With VBA Macro Code

However, any time after the initial click on the combo box (as long as the PowerPoint file stays open) the drop down will act as normal and expand fully as shown below.

Filter Microsoft Excel Data In A PowerPoint Presentation With VBA Macro Code

I'm not 100% sure if this is a bug on Microsoft's end or if there is a better way to write the code in this article to prevent this from happening.  If you really don't want to have to re-click on the combo box the first time around during your presentation, just make sure you give the combo box a click (in Presentation Mode) before you begin presenting and everything will work fine.  To all you PowerPoint Gurus out there, please leave a comment below if you know why this "bug" is happening and if there is any workaround.

How Will You Use This?

As I had mentioned at the beginning of this post, this was an idea suggested by one of my many readers. I have not personally used or needed this functionality during my day job however I could see it being pretty useful in certain situations.  Let me know how you intend to use this functionality in the comments section below.  I'm sure your ideas will inspire others to create awesome functioning presentations that will blow everyone away!  Be sure to download the example files below to take my example for a test drive.

Download The Example

Make sure you watch the video in the post and update the PowerPoint VBA code with the file path where you end up storing the Excel file on your computer.  This will tell PowerPoint where the Excel file is located so it can copy-in the filtered data.

As always, in order to download this article's example file you will need to be a subscriber of my free tips newsletter. If you click the green button below you can easily sign up and you will be emailed the password to get into the subscribers-only area of this website.

 
     Already Subscribed? Click HERE to log-in to the "Example Files" section

     Already Subscribed? Click HERE to log-in to the "Example Files" section

 

How Do I Modify This To Fit My Specific Needs?

Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, you name it

I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).

I wish you the best of luck and I hope this tutorial gets you heading in the right direction!

Chris "Macro" Newman :)