asked on

toggle button to switch between built-in chart styles

Dear Experts:

below macro applies a built-in chart style (ChartStyle = 27) to all charts of the active sheet.

I got another two separate macros to apply built-in chart styles (ChartStyle = 25, ChartStyle = 28) to all the charts.

I wonder whether it is possible to use just one single toggle button to switch between the three built-in chart styles, i.e. say ...

built-in style '25' is applied to all the charts, pressing the toggle buttion applies built-in ChartStyle 28, pressing this toggle buttion again applies ChartStyle 27. Another activation of the toggle button applies ChartStyle 25 again and so forth.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

Sub ChangeColor_BLUE()
Dim Chtobj As ChartObject
Dim i As Integer
Dim k As Integer
Dim j As Integer
Dim l As Integer


If MsgBox("Would you like to add blue colouring to all charts?", vbQuestion + vbYesNo, "Apply Blue STyle") = vbNo Then
Exit Sub
End If


If ActiveSheet.ChartObjects.Count = 0 Then
MsgBox "No Charts existing", vbInformation, "There are noch charts on the active sheet!"
Exit Sub
End If


For Each Chtobj In ActiveSheet.ChartObjects

With Chtobj.Chart
.ChartStyle = 27
 End With
 i = i + 1
Next


End Sub

Open in new window

krishnakrkc

Hi,

Insert a shape and rename it as 'ToggleChartStyle'

Right click on the shape > Assign Macro > New

paste the code there. Now click on the shape to toggle the chart style.

Kris

Sub ToggleChartStyle_Click()
    Dim strLastStyle    As String
    Dim chtObj          As ChartObject
    Dim Cnt As Long, i  As Long
    
    Cnt = ActiveSheet.ChartObjects.Count
    If Cnt = 0 Then Exit Sub
    
    On Error Resume Next
    strLastStyle = ActiveSheet.Shapes("ToggleChartStyle").AlternativeText
    On Error GoTo 0
    
    If Len(strLastStyle) = 0 Then
        With ActiveSheet
            For i = 1 To Cnt
                .ChartObjects(i).Chart.ChartStyle = 25
            Next
        End With
        ActiveSheet.Shapes("ToggleChartStyle").AlternativeText = 25
    Else
        If strLastStyle = "25" Then
            strLastStyle = "27"
            ActiveSheet.Shapes("ToggleChartStyle").AlternativeText = 27
        ElseIf strLastStyle = "27" Then
            strLastStyle = "28"
            ActiveSheet.Shapes("ToggleChartStyle").AlternativeText = 28
        ElseIf strLastStyle = "28" Then
            strLastStyle = "25"
            ActiveSheet.Shapes("ToggleChartStyle").AlternativeText = 25
        End If
        With ActiveSheet
            For i = 1 To Cnt
                .ChartObjects(i).Chart.ChartStyle = CLng(strLastStyle)
            Next
        End With
    End If
            
End Sub

Open in new window

Andreas Hermle

ASKER

Dear Kris,

thank you very much for your quick help. I am afraid to tell that your code throws error messages. I am gonna do some trouble shooting and then get back to you with a detailed feedback.

thank you. Regards, Andreas

ASKER CERTIFIED SOLUTION

krishnakrkc

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Andreas Hermle

ASKER

Hi Kris,

this is great! Works like a charm! Thank you very much for your great and professional help. I really appreciate it.

Regards, Andreas