How to: Find All the Sparklines on a Sheet
Last modified: June 30, 2011
Applies to: Excel 2013 | Office 2013 | VBA
The following code example uses a list box on a user form to display all of the sparkline groups on the active sheet. When you click one of the sparkline groups in the list box, the sparkline group is selected on the sheet.
This example requires a user form named SparklineForm, a list box on the user form named SparklineListBox, and a button on the user form named CloseBtn.
In the Visual Basic Editor, insert a Module and copy and paste the following code. This code shows the user form.
In the Visual Basic Editor, right-click the SparklineForm form, select View Code, and copy and paste the following code.
The UserForm_Activate procedure iterates through all the sparkline groups on the active sheet and gets the addresses of the sparkline groups by using the Address property of the Range object. The address is then added to the list box.
The SparklineListBox_Click procedure is called when you click the address of a sparkline group in the list box. This procedure activates the selected sparkline group on the sheet by using the Activate method of the Range object.
The CloseBtn_Click procedure is called when you click the button on the user form, and it closes the user form.
Private Sub UserForm_Activate() 'The sparkline group Dim oSparkGroup As SparklineGroup 'Loop through all the sparkline groups on the sheet For Each oSparkGroup In ActiveSheet.Range("A:XFD").SparklineGroups 'For each sparkline group found, add the address to the listbox SparklineListBox.AddItem oSparkGroup.Location.Address(, , , True) Next oSparkGroup End Sub Private Sub SparklineListBox_Click() 'Activate the selected range that has the sparklines Range(SparklineListBox.Value).Activate End Sub Private Sub CloseBtn_Click() 'Close the userform Unload Me End Sub