Comment from Gunjan
Time: Thursday, April 9, 2009, 3:52 am

Check my article on http://www.webanalyticsindia.com/2008-12-17/dashboard-design-tricks-using-combo-box-for-a-neat-treat/

Comment from Lawrence
Time: Tuesday, July 7, 2009, 8:23 pm

Hi,

Your Web pages have been VERY helpful. Thank you!

One question on the “Interactive Charts with Checkboxes and VBA” macro. I copied it to my own worksheet and changed it from hide column to hide row. But it only works successfully when I manually run the macro and not automatically when a cell in the true/false range is changed.

What am I doing wrong? I’m not very familiar with VBA, so be gentle.

Thanks again.

Lawrence

Comment from Lawrence
Time: Wednesday, July 8, 2009, 11:21 am

Jon,

I changed the range name and the range in both the worksheet and in the VB editor. But when I change one of the true/false cells nothing happens. The macro will only run (and run correctly) after I click on the run macro button up on my toolbar. How do I get the macro to run the instant one of the true/false cells is changed?

Here’s more detail… My true/false range is D36:D39 and it is named “Chart5TrueFalseSeriesRange”. Then, I copied your macro into a module 1 of my workbook and changed 2 things: the macro name; and rCell.EntireColumn.Hidden to rCell.EntireRow.Hidden.

So, the macro looks like this:

Option Explicit

Sub Chart5HideSeriesCheckbox()
Dim rCell As Range
For Each rCell In ActiveSheet.Range(“Chart5TrueFalseSeriesRange”).Cells
rCell.EntireRow.Hidden = Not rCell.Value
Next
End Sub

If it helps, I can send you the 1-page workbook.

Thanks again!

Lawrence

Comment from Lawrence
Time: Wednesday, July 8, 2009, 11:22 am

Oh, one other detail. My check boxes are ActiveX controls.

Lawrence

Comment from Lawrence
Time: Wednesday, July 8, 2009, 5:56 pm

Jon,

I entered the code as instructed and when I click on the checkbox I get “Compile error: Syntax error”.

The original code is located in Module1 and the new code to fire the macro is located in parent worksheet in the VB Editor called Sheet 3 (Data).

Lawrence

Comment from Lawrence
Time: Thursday, July 9, 2009, 11:26 am

Jon,

When I click on the checkbox1 and I get the error, it opens up the VB Editor and the second line of the macro is highlighted, Chart5HideSeriesCheckbox().

The whole macro in Sheet3(Data) is 3 lines:

Private Sub CheckBox1_Click()
Chart5HideSeriesCheckbox()
End Sub

The macro in Module 1 is unchanged from the comment above.

Lawrence

Comment from Lawrence
Time: Thursday, July 9, 2009, 12:18 pm

SUCCESS!

Thank you.

Now that I have the checkboxes working on the Data worksheet, I will need to move them to the worksheet where the chart will be moved to. Will the checkboxes be able to reveal/hide rows on the “Data” worksheet if they are located on the “Dashboard” worksheet? I tried it after I moved the code to the Dashboard tab; the linked cell works on the Data tab, but the row will not hide. Does that have something to do with the part Module1 code that says ActiveSheet? If so, do I simply replace that word with “Dashboard”?

Man, this is so much fun when cool tools like this work so beautifully!

Lawrence

Comment from Lawrence
Time: Thursday, July 9, 2009, 2:41 pm

Jon,

Everything is now working perfectly. YAY!!!

My only question is whether the code that I changed slightly makes for “good code”. I simply deleted the text “ActiveSheet.” from before the Range stuff in the 3rd row:

Sub Chart5HideSeriesCheckbox()
Dim rCell As Range
For Each rCell In Range(“Chart5TrueFalseSeriesRange”).Cells
rCell.EntireRow.Hidden = Not rCell.Value
Next
End Sub

Will that change result in problems down the road?

Lawrence

Comment from DaleW
Time: Wednesday, August 5, 2009, 1:39 pm

Jon – Thank you for providing the “missing manual” on how to make great Excel charts. This chapter on designing interactive charts — with or without any VBA code — was perfect for my main project today.