Chart Template Based On Data Validation

[jakeenos]

Hello,

Firstly, I'd like to thank the people that have provided solutions to threads on this website. It has been incredibly beneficial to me.

So, here is my issue. I'm looking for a way to have a chart change based upon data validation. The change would be from one chart template to another chart template.

I have a report that needs the same charts, but they need to be formatted for a white background and a black background.

I have a template for each scenario, but I would like to have some way to have them change without having to manually update each one by going through the 'Change Chart Type' window.

I figured this could be solved with data validation, but it may be the case that a macro would be more applicable.

 

[kevin9999]

Lots of detail missing, but I hope the following demo will put you on the right track.
Assumptions:
The data validation cell on your sheet is B1 (change to suit).
The chart you want to change is the first chart on the sheet (index 1 - change to suit).
The 2 templates have the same names as the data validation choices - i.e. when you select "Chart1" in B1 then the template you have named "Chart1" is used, likewise with Chart2.
The path to you chart templates will have to be changed to match your actual template locations.

So with all of the above taken into account, put the following worksheet change event code in the sheet module of the sheet with the chart in question (right-click the sheet tab name, select View Code & put the code in the window that appears). You'll need to save the file in macro-enabled or binary format for it to work.
The code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("B1"), Target) Is Nothing Then
        On Error GoTo Escape
        Application.EnableEvents = False
        Dim myChart As ChartObject, Temp As String
        Set myChart = ActiveSheet.ChartObjects(1)
        Temp = [B1].Value
        ActiveSheet.ChartObjects(1).Select
        ActiveChart.ApplyChartTemplate ("C:\Users\kevin\AppData\Roaming\Microsoft\Templates\Charts\" & Temp & ".crtx")
        [B1].Select
    End If
    
Continue:
    Application.EnableEvents = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
End Sub

When Chart1 is selected in B1 you get this:


When Chart2 is selected in B1 you get this:

 

[kevin9999]

Code slimmed down a bit:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("B1"), Target) Is Nothing Then
        On Error GoTo Escape
        Application.EnableEvents = False
        ActiveSheet.ChartObjects(1).Select
        ActiveChart.ApplyChartTemplate ("C:\Users\kevin\AppData\Roaming\Microsoft\Templates\Charts\" & [B1] & ".crtx")
        [B1].Select
    End If
Continue:
    Application.EnableEvents = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
End Sub

 

[jakeenos]

Code slimmed down a bit:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("B1"), Target) Is Nothing Then
        On Error GoTo Escape
        Application.EnableEvents = False
        ActiveSheet.ChartObjects(1).Select
        ActiveChart.ApplyChartTemplate ("C:\Users\kevin\AppData\Roaming\Microsoft\Templates\Charts\" & [B1] & ".crtx")
        [B1].Select
    End If
Continue:
    Application.EnableEvents = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
End Sub

Hey Kevin,

Thank you for your reply! I really appreciate the help. I'm a novice when it comes to marcos, so you'll have to excuse my confusion.

I've provided a picture to showcase your code that I've adjusted as well as the sheet in question.

I've placed the worksheet change event code in the sheet module of the sheet with the chart in question.

I've changed the names of the chart templates to match the data validation list in cell B20.

I've saved the file as a macro-enabled file.

This code works great for the first chart! Is there some adjustment that can be made to have this work for two charts? Each tab that I need this for has two charts.

 

[jakeenos]

Code slimmed down a bit:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("B1"), Target) Is Nothing Then
        On Error GoTo Escape
        Application.EnableEvents = False
        ActiveSheet.ChartObjects(1).Select
        ActiveChart.ApplyChartTemplate ("C:\Users\kevin\AppData\Roaming\Microsoft\Templates\Charts\" & [B1] & ".crtx")
        [B1].Select
    End If
Continue:
    Application.EnableEvents = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
End Sub

Hey Kevin,

Good news! I was able to adjust the code to make it work for both. Thank you so much for your help!

 

[kevin9999]

Happy to hear you got it working, it's always more satisfying when we work it out for ourselves. Welcome to the forum, and thanks for the feedback