VBA excel not run unless i click play button

Question

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

I can only assume that you are trying to create a Worksheet_Change event triggered by F8 — jcarroll, Jun 14 '16 at 16:38

score 5 · Answer 1 · answered Jun 14 '16 at 16:48

5

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.

answered Jun 14 '16 at 16:48

Mathieu Guindon

69,817
8
107
235

Can you expand a little, or direct to some resources, detailing why to avoid using logic in event handlers? It's the first time I've seen this mentioned, and I'd like to know more :D – BruceWayne Jun 14 '16 at 17:08
Ah, good points. It actually reminded me that one of my workbooks has a pretty convoluted `Worksheet_Change` event that would benefit from breaking out into their own subs. Thanks for your comments, helped me out! – BruceWayne Jun 14 '16 at 17:40

score 1 · Answer 2 · edited May 23 '17 at 10:33

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.

score 1 · Accepted Answer · answered Jun 14 '16 at 16:43

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.

Should maybe change `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. — Tim Williams, Jun 15 '16 at 00:01

VBA excel not run unless i click play button

3 Answers3