VBA Excel: Toggle individual objects on and off | Excel forum

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!