Scroll To Chart Based on Form Control Combo Box Selection
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 dropdown 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 SubHow 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…
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)
4. Click OK
Now every time you make a selection in the Combo Box’s dropdown 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 let’s 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 the default)
The reference input allows you to designate an object or cell to 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 the 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.
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.RemoveAllItemsThe 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 IfRepopulating 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 = myArrayReal 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!
Download Example Excel File
If you would like to get a copy of the Excel file I used throughout this article, feel free to directly download the spreadsheet by clicking the download button below.
Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.