Solved: Buttons

[MonteCristo]

Hello,

I am new to VBA, just started playing around in it last month and I have to say I really like it so far. It's nice, the syntax is extremely easy to understand and read and I kinda wish everything in Computer Science would be written in VB :beerchug: .

Anyway, I hit a brick wall with something. Let's say I have a chart in the Worksheet. I would like to add a button (from the developer ribbon) that can toggle some parts of the chart on/off. For now, how do I make the legend appear and disappear with a button? I've searched a lot and the code should look like this:

Sub Button1_Click()

With Worksheets("Products").ChartObjects(1).Chart
If Button1.Value = True Then
.HasLegend = True
Else
.HasLegend = False
End If
End With

End Sub

The compiler doesn't recognize Button1, "Variable not defined". How do I make a toggle button in VBA without the User Forms, straight from the Developer Ribbon?

[mbarron]

Try this instead:
Sub Button1_Click()
With Sheets("Sheet1").ChartObjects(1).Chart
.HasLegend = Not .HasLegend
End With
End Sub

[MonteCristo]

Hey, thanks a lot! Nice thinking when it's a boolean attribute. But what if I want to toggle between actual values? Like toggle type of charts (3dpie or line chart) or what values it chooses to show in the chart, values in column A or in column B, I mean modifying the ".SetSourceData" with a click of a button.

Thanks again.

[mbarron]

For changing the chart type, you can use:
Sub Button2_Click()
Dim i As Integer
For i = 1 To Sheets("sheet1").ChartObjects(1).Chart.SeriesCollection.Count
With Sheets("sheet1").ChartObjects(1).Chart.SeriesCollection(i)
If .ChartType = xlPie Then
.ChartType = xlColumnClustered
Else
.ChartType = xlPie
End If
End With
Next
End Sub
Search help for XlChartType Enumeration to see the variables for chart types. The above code changes all series in a chart - it will work for on series charts as well.

And something like this to change the columns:
Sub Button2_Click()
Dim i As Integer
For i = 1 To Sheets("sheet1").ChartObjects(1).Chart.SeriesCollection.Count
With Sheets("sheet1").ChartObjects(1).Chart.SeriesCollection(i)
If InStr(.Formula, "$A") > 0 Then
.Formula = Replace(.Formula, "$A", "$B")
Else
.Formula = Replace(.Formula, "$B", "$A")
End If
End With
Next
End Sub

[Bob Phillips]

This doesn't address your question about firing a toggle button from the ribbon.

How au-fait are you with the ribbon, XML and callbacks?

[MonteCristo]

It is indeed true that I haven't made it work with changing the chart to show the different sales for different months, but everything else works superbly (thanks mbarron). Maybe I can explain this further.

Let's say I have in Range A1:A13 the months of the year; in Range B1:B13 sales for a product A and in C1:C13 sales for a product B. I insert a chart normally from Excel, with the Range A1:B13 selected. What I want is the button to switch between the 2 products... in other words, switch between A1:B13 and (A1:A13,C1:C13). Any new ideas? :hi:

[Bob Phillips]

Okay, you seem set, I just wondered if you were still needing to pursue the wonders of progamming the ribbon.

[Bob Phillips]

It is indeed true that I haven't made it work with changing the chart to show the different sales for different months, but everything else works superbly (thanks mbarron). Maybe I can explain this further.

Let's say I have in Range A1:A13 the months of the year; in Range B1:B13 sales for a product A and in C1:C13 sales for a product B. I insert a chart normally from Excel, with the Range A1:B13 selected. What I want is the button to switch between the 2 products... in other words, switch between A1:B13 and (A1:A13,C1:C13). Any new ideas? :hi:

Just re-read this. All you have to do is to change the code supplied to



Sub Button2_Click()
Dim i As Integer
For i = 1 To Sheets("sheet1").ChartObjects(1).Chart.SeriesCollection.Count
With Sheets("sheet1").ChartObjects(1).Chart.SeriesCollection(i)
If InStr(.Formula, "$B") > 0 Then
.Formula = Replace(.Formula, "$B", "$C")
Else
.Formula = Replace(.Formula, "$C", "$B")
End If
End With
Next
End Sub

[MonteCristo]

Thanks. I started reading around and I figured it out. I used a global boolean variable that always changes its value depending on what "sales" range is selected. Used mbarron's idea from before... thanks a lot you guys.