Simon has attended:
Excel VBA Intro Intermediate course
Excel VBA Advanced course
VBA Excel: Toggle individual objects on and off
Guys,
I have charts where you can turn lines and bars on and off using toggle buttons or check boxes. I am trying to VBA the buttons to also turn off the equivalent line (object) in manually produced keys to the charts.
The first question is - do you think that it is possible to achieve this?
I have tried a variety of alternatives within the following buggy syntax:
Private Sub NamedToggleButton_Click()
If NamedToggleButton = True Then
[
RE: VBA Excel: Toggle individual objects on and off
Hi Simon
Thank you for your question
This is a fairly complicated question, with a number of possible lines of inquiry. It would therefore be useful if you could email me a copy of the workbook in question so I can explore the issues thoroughly.
My email address is stephen@microfttraining.net
Regards
Stephen
RE: VBA Excel: Toggle individual objects on and off
Stephen,
As ever, the solution turned on a single character. My investigations led me to believe that the objects that I was working on were "Line72", "Line78" and their colleagues. Eventually I added a space - "Line 72". "Line 78" etc. and everything fell in to place.
ActiveSheet[or other WorkSheet ref].Shapes("Line 72").Visible = True is the correct syntax.
For each time it is clicked (ie ON or OFF). the following sub routine causes a toggle button (PrimaryProjection) to change colour and text, to change the value of a cell which affects a formula that results in a line showing or not showing on a chart, AND to toggle on or off the appropriate line object in a key (which is not integral to the chart).
Sub PrimaryProjection_Click()
If PrimaryProjection = True Then
With PrimaryProjection
.BackColor = RGB(35, 230, 0)
.Caption = "ON"
.Font = "Verdana"
.Font = "Bold"
.ForeColor = RGB(50, 100, 50)
.Shadow = False
End With
Worksheets("Data Factory").Range("B14") = True
ActiveSheet.Shapes("Line 72").Visible = True
Else:
With PrimaryProjection
.BackColor = RGB(255, 150, 200)
.Caption = "OFF"
.Font = "Verdana"
.Font = "Bold"
.ForeColor = RGB(200, 30, 30)
.Shadow = False
End With
Worksheets("Data Factory").Range("B14") = False
ActiveSheet.Shapes("Line 72").Visible = False
End If
End Sub
The worksheet currently contains 3,337 rows of 63 columns of data. So that's 210,231 data points that I would have to fictionalise in a coherent fashion (as much of the functionality of the worksheet is dependent on relationships between various data) in order to send it. In due course the worksheet will be publicly available, at which point I will try to remember to forward a copy.
Cheers,
S!
RE: VBA Excel: Toggle individual objects on and off
Stephen,
I was not permitted to share the data within the spreadsheet until recently. It is now publicly available via the South East Public Health Observatory at http://www.sepho.org.uk/viewResource.aspx?id=13062
... can you SEEIT? (You'll understand the question if you follow the link!)
For historic reasons, and being part of a wider tool kit, the spreadsheet is a hybrid of formulae and VBA. Nothing is hidden, although worksheet tabs and other spreadsheet periphery are switched off when the spreadsheet loads. Also, it is developed in Excel 2003, some of the macros are not reliable in Excel 2007.
Please feel free to inundate me with suggestions for improvements! (The tool continues to be developed, both in terms of scope and functionality. I am currently looking at using VBA to dynamically create the chart, although adjusting the time period on the X axis is tricky (as the date series can include zeros), and there is an aim to introduce a dashboard in order to compare a range of indicators for an area or cohort (eg: older people, children, social care users etc.)
Enjoy,
S!
