?
Solved

toggle button to switch between built-in chart styles

Posted on 2011-05-06
4
Medium Priority
?
280 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:AndreasHermle
  • 2
  • 2
4 Comments
 
LVL 18

Expert Comment

by:krishnakrkc
ID: 35707860
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

0
 

Author Comment

by:AndreasHermle
ID: 35710179
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
0
 
LVL 18

Accepted Solution

by:
krishnakrkc earned 2000 total points
ID: 35711048
Hi,

PFA. I replaced the shape with a command button.

HTH

Kris
ToggleChartStyle.xlsm
0
 

Author Closing Comment

by:AndreasHermle
ID: 35715859
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
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question