Select chart within userform

Question

I am looking to write a macro which copies formatting from one graph and applies it to multiple other graphs.

What I am struggling to do is determine a way to allow the user to set the template chart and then select the multiple other charts. While this could be done with a combo box if the user knew the chart name, I am trying to do it without them knowing the chart name.

As such I was thinking of having a user dialog box where the user can select the base chart, and then select the charts to apply the formatting to. Just like refedit for a range. However I cannot figure out how to reference to a graph from within a user form.

Can this be done, and if so, how?

Answer 1

Here is what will get you started.

Place Two ComboBoxes and two Image Controls on the userform.

Let's say your worksheet looks like this

In the UserForm_Initialize() event populate the Chart names in both the comboboxes. For example

Dim ws As Worksheet

'~~> Prepare your form
Private Sub UserForm_Initialize()
    Set ws = ThisWorkbook.Sheets("Sheet1")

    Dim ChartObj As ChartObject

    For Each ChartObj In ActiveSheet.ChartObjects
        ComboBox1.AddItem ChartObj.Name
        ComboBox2.AddItem ChartObj.Name
    Next ChartObj
End Sub

So when you run the form, it will look like this

In the click event of the comboboxes, use the Stephen Bullen's PastePicture code from HERE to show the chart in the userform. For example

Private Sub ComboBox1_Click()
    ws.Shapes(ComboBox1.Value).CopyPicture
    Set Me.Image1.Picture = PastePicture(xlPicture)
End Sub

Private Sub ComboBox2_Click()
    ws.Shapes(ComboBox2.Value).CopyPicture
    Set Me.Image2.Picture = PastePicture(xlPicture)
End Sub

This is how the form will look.

From there on, Now you have the names of the charts. Simply use them to work as you please.

Hope this helps.

Answer 2

The following code should allow you to do stuff with the selected chart area(s), where you can either select one or many charts:

Public Sub ProcessSelectedCharts()
    Dim i As Integer
    Dim chart_obj As ChartObject
    Dim chart_area As chartArea

    If TypeOf Selection Is DrawingObjects Then
        For i = 1 To Selection.Count
            If TypeOf Selection(i) Is ChartObject Then
                Set chart_obj = Selection(i)
                Set chart_area = chart_obj.Chart.chartArea
                Call ProcessChart(chart_area)
            End If
        Next i
    ElseIf TypeOf Selection Is chartArea Then
        Set chart_area = Selection
        Call ProcessChart(chart_area)
    End If

End Sub

Public Sub ProcessChart(obj As chartArea)
    ' Do something...
End Sub

You may want to refine this a little, i.e. this should work if the user selects the actual charts, but may fail if he only selects a particular element within the chart.

Ok, the next question is, when do you invoke this from your user form. So first of all, your user form should be modal of course, to allow the user the select anything. So how do you notice when the users actually selects anything? I can think of three methods, and I'll list them from best to worst (the last 2 only described very briefly as I wouldn't recommend using them):

1. Use the "Worksheet_SelectionChange" event on your worksheet, and have it call a method within your userform to inform it that the selection has changed. Now you'll just need to check if, and which charts have been selected (see code above), and run your algorithm.

2. Run a timer in your userform and regularly check the active selection.

3. Hook mouse events by DLL calls and register any mouse clicks, then check for selection changes.

That should allow you to create a ref-edit like feature for selecting charts on your worksheet from a userform.