I don't know where i have gone wrong. The Script works but it doesn't automatically run unless i go into the VBA application and click the play button for the script to run. Can someone help?
Private Sub HideChart1()
If ActiveWorkbook.Sheets("User Interface").Range("F8") = "Yes" Then
ActiveWorkbook.Sheets("Sheet1").ChartObjects("Chart 6").Visible = True
Else
ActiveWorkbook.Sheets("Sheet1").ChartObjects("Chart 6").Visible = False
End If
End Sub
Handle SheetChange, and call your procedure in there.
Private Sub Worksheet_Change(ByVal Target As Range)
HideChart1
End Sub
Done. Don't implement logic in event handlers, it's the first Gate of Hell and spaghettification. Having a separate, dedicated procedure for it is perfect - all you need is something that calls it automatically.
If HideChart1 is implemented in its own standard module, you'll need to make it Public to be able to call it from a worksheet's code-behind; add Option Private Module at the top of that module to avoid exposing members as macros and/or worksheet functions.
Worksheet_Change event that would benefit from breaking out into their own subs. Thanks for your comments, helped me out!You want to use a Worksheet Change event.
Untested, but should work. Put this in your "User Interface" worksheet module.:
Private Sub Worksheet_Change(ByVal target As Range)
If target = Range("F8") And target.Value = "Yes" Then
ActiveWorkbook.Sheets("Sheet1").ChartObjects("Chart 6").Visible = True
Else
ActiveWorkbook.Sheets("Sheet1").ChartObjects("Chart 6").Visible = False
End If
End Sub
With Worksheet Change, I don't think (keyword "think") you need to specify the worksheet the Range("F8") is expected to be on, since it's by definition, the activesheet. If you want though, add the sheet name before that to be sure.
ActiveWorkbook to ThisWorkbook for added robustness (assuming the chart is in the same workbook as the code) Event handlers can run without the workbook necessarily being active.If my comment is correct, then you'll need something like this:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Target.Worksheet.Range("F8")) Is Nothing Then
If Target.Worksheet.Range("F8").Value = "Yes" Then
ActiveWorkbook.Sheets("Sheet1").ChartObjects("Chart 6").Visible = True
Else
ActiveWorkbook.Sheets("Sheet1").ChartObjects("Chart 6").Visible = False
End If
End If
End Sub
You can find more information about the worksheet change event here automatically execute an Excel macro on a cell change
The key takeaway from that question, is to put the code in the worksheet module, not a regular module, or the workbook module.
