Select a chart in a custom form

Question

I want to write a macro that copies formatting from one graph and applies it to several other graphs.

What I'm trying to do is identify a way that allows the user to set up a template diagram, and then select several other diagrams. Although this can be done using a combined field, if the user knew the name of the chart, I try to do this without knowing the name of the chart.

As such, I thought that there is a user dialog in which the user can select the base chart, and then select the chart to apply formatting. Just like refeditfor the range. However, I cannot figure out how to link to a chart from a user form.

Can this be done, and if so, how?

Answer 1

Here is what you will start.

Place two combo boxes and two image controls in a custom shape.

Let's say your worksheet looks like this:

In the event, UserForm_Initialize()specify the chart names in both lists. for instance

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

In the click event from combobox lists, use the Stephen Bullen PastePicture code from HERE to display the chart in a custom form. for instance

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, you now have the names of the diagrams. Just use them to work as you please.

Hope this helps.

Answer 2

() , :

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

, , , , .

, : . , , , , , . , , -? , ( 2 , ):

• "Worksheet_SelectionChange" , . , (. ) .

• .

• DLL , .

ref- .