Edit

Find All the Sparklines on a Sheet

  • Article

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.

Sub ShowUserForm()
    SparklineForm.Show
End Sub

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

See also

SparklineGroup Object Programming With Sparklines In Excel

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.