Scroll To Chart Based on Form Control Combo Box Selection

VBA Form Control Combo Box Scroll To Chart Graph

In my previous post VBA For Excel's Form Control Combo Boxes I showed you all the different actions you can automate while combining combo boxes with VBA code.  Now I'm going to put some of that code  to use and answer a question that came in from Terry (Las Vegas, NV).  He was wanting to create a drop down navigation system allowing him to easily jump to any graph on his spreadsheet.  Below is what I came up with.

VBA Macro We Will Be Using

Below is the VBA code that was assigned to run whenever the combo box's selection is changed.

Sub NavigateToChart()

'PURPOSE: Navigate to selected Graph Name from Form Control Combo Box
'SOURCE: www.TheSpreadsheetGuru.com

Dim myDropDown As Shape
Dim ChartName As String
Dim DropDownName As String
Dim cht As ChartObject
Dim sht As Worksheet
Dim ListPosition As Long
Dim x As Long
Dim myArray() As Variant

'Optimize Settings
  Application.ScreenUpdating = False

'Redimension Array
  x = 0
  ReDim myArray(x To x)

'Retrieve name of object that called this macro
  DropDownName = Application.Caller

'Set Object Variables
  Set sht = ActiveSheet
  Set myDropDown = sht.Shapes(DropDownName)

'Retrieve User Selection
  ListPosition = myDropDown.ControlFormat.ListIndex
  ChartName = myDropDown.ControlFormat.List(ListPosition)
  
'Navigate to Selected Chart Name
  If ChartName <> "Go To" Then
    On Error GoTo ChartDoesNotExist
      Application.GoTo sht.ChartObjects(ChartName).TopLeftCell, True
    On Error GoTo 0
    
    On Error Resume Next
      Application.GoTo ActiveCell.Offset(-1, 0), True 'Ensure activecell is out of the way
    On Error GoTo 0
  End If
    
'Clear Drop Down List
  myDropDown.ControlFormat.RemoveAllItems

'Error Handler
ChartDoesNotExist:
  If Err.Number = -2147024809 Then
    MsgBox "The chart name you selected does not exist. Repopulating Drop Down list.", _
      vbExclamation, "Chart Name Does Not Exist"
    On Error GoTo 0
  End If
  
'First position needs to say "Go To"
  myArray(x) = "Go To"
  
'Loop Through each chart
  For Each cht In sht.ChartObjects
    x = x + 1
    ReDim Preserve myArray(0 To x)
    myArray(x) = cht.Name
  Next cht

'Fill Drop Down List
  myDropDown.OLEFormat.Object.List = myArray
  
'Select First List Item
  myDropDown.ControlFormat.ListIndex = 1

'Optimize Settings
  Application.ScreenUpdating = True
  
End Sub

How It Works

The simple answer is you can assign a magic (and extremely variable) macro to run with a combo box form control.  This macro is called NavigateToChart and you can make it a combo box triggered macro by doing the following:

1. Right Click on the Form Control Combo Box (you can insert a combo box through the Controls group on the Developer Tab)

2. Select Assign Macro...

Excel Assign VBA Macro To Control Form Combo Box

3. In the Assign Macro dialog box change the Macros In to state This Workbook

4. Select the macro named NavigateToChart in the Macro Name listbox (NOTE: you must paste the above VBA code into a module to see it appear in this dialog box)

Excel Assign VBA Macro To Control Form Combo Box

4. Click OK

Now every time you make a selection in the Combo Box's drop down menu, the NavigateToChart macro will run.

Explaining The VBA

The easiest way to explain how this VBA macro works is to break it down into a couple of sections.  So lets break it down into three:

  • Navigating To The User's Selection
  • The Error Handler
  • Repopulating The Drop Down List

Navigating To The User's Selection

To navigate to the desired chart the macro code makes use of the GoTo method.  GoTo has two inputs

  • Reference
  • Scroll? (optional - False is default)

The reference input allows you to designate an object or cell in which you want to navigate to.  In this case we are using a Chart Object reference.  The optional Scroll input accepts a Boolean (True or False) value and determines if you want to visually scroll to the object or just select it.  In this example you what to see the graph so you need to call out the scroll input and change it to a True value.

Next, you can add in an offset statement to move the selected cell out of the way.  This allows the user to see the entire graph without any visual noise.  By wrapping the offset statement with an error handler, you can handle instances when selected cell is on row 1 (there's no such thing as row 0 in Excel).  Below are examples of how the code would run without and with this Offset line of code.

Without the Offset line of VBA code

Without the Offset line of VBA code

With the Offset line of VBA code (much easier to see graph!)

With the Offset line of VBA code (much easier to see graph!)

The last section clears out the combo box's list data as the macro prepares to refresh the contents in the drop down list with all the current chart names on the spreadsheet.

'Retrieve User Selection
  ListPosition = myDropDown.ControlFormat.ListIndex
  ChartName = myDropDown.ControlFormat.List(ListPosition)
  
'Navigate to Selected Chart Name
  If ChartName <> "Go To" Then
    On Error GoTo ChartDoesNotExist
      Application.GoTo sht.ChartObjects(ChartName).TopLeftCell, True
    On Error GoTo 0
    
    On Error Resume Next
      Application.GoTo ActiveCell.Offset(-1, 0), True 'Ensure activecell is out of the way
    On Error GoTo 0
  End If
    
'Clear Drop Down List
  myDropDown.ControlFormat.RemoveAllItems

The Error Handler

It might seem a little unusual to see the error handler stuck in the middle of the subroutine but hopefully I can convince you to see the method to this madness.  The error handler only comes into play when a chart name that does not exist is selected.  This can occur when the combo box list has not been refreshed and:

  • A chart is deleted
  • A chart is renamed

So why stick the error handler in the middle of the code instead of at the end of the subroutine?  This is because even if the code cannot find a chart, you still want the macro to refresh the drop down list so that it's contents are always up to date for next time.  This is why the macro deletes and repopulates the list of chart names every time it is executed.

'Error Handler
ChartDoesNotExist:
  If Err.Number = -2147024809 Then
    MsgBox "The chart name you selected does not exist. Repopulating Drop Down list.", _
      vbExclamation, "Chart Name Does Not Exist"
    On Error GoTo 0
  End If

Repopulating The Drop Down List 

To repopulate the combo box's list we are going to use an Array variable to store all the chart names on the spreadsheet.  I typically associate a one dimensional Array variable with a grocery list.  We will list out everything we want and can then reference the items by referring to its position (number) in the list.  

Unlike typical lists, array lists start at row 0 by default (hence why we made variable x = 0 at the beginning of the code).  The first item we want on our list is the phrase "Go To" (the first line of code sets position 0 = "Go To").  Next we will loop through every chart on the spreadsheet and add the chart names to our array variable (adding 1 to the variable x every time so you don't override the chart names).  Notice how you have to Redimension (aka resize) the array variable, allowing it can fit one more item inside it's list every iteration of the loop.  Also notice the use of the command Preserve.  This tells the code to keep the data stored in the array while redimming it (by default when you Redim an array it clears out all of the data stored in inside of it).

Once we finally have all our chart names stored in the array variable, its contents will look similar to this:

myArray("Go To", "Chart 1", "Chart 2", "Chart 3")

We can then fill the combo box's drop down list with the data  from our array variable!

'First position needs to say "Go To"
  myArray(x) = "Go To"
  
'Loop Through each chart
  For Each cht In sht.ChartObjects
    x = x + 1
    ReDim Preserve myArray(0 To x)
    myArray(x) = cht.Name
  Next cht

'Fill Drop Down List
  myDropDown.OLEFormat.Object.List = myArray

Real World Applications?

I personally use this code concept as an efficiency mechanism. There are numerous workbooks I have where the data in them is solely meant to populate PowerPoint slides for monthly presentations. This means I could have a graph for each store's performance, each product's sales figures, etc... inside a single worksheet of my workbook. This essentially equates to 20-50 charts inside a single spreadsheet tab. Having a navigation setup like discussed in this blog post saves me from having to scroll forever and guess where a chart is located. This can save time and frustration if I have an inquiry about a specific graph from a manager. Plus it has a wow factor if someone is looking over your shoulder!

I'm sure there are a ton of uses for this technique. Let me know how you would use it or if you would make any modifications to the macro code. I look forward to reading your responses and feel free to download my example workbook below to save time!

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 :)