Hide Chart with Cell Change

[VBAMePlease]

Current Code:

Sub Chart_Toggle()

If Worksheets("Inputs").Range("F74").Value = 1 Then
    
    Worksheets("Structuring").ChartObjects("Chart 3").Visible = False


ElseIf Worksheets("Inputs").Range("F74").Value = 0 Then
    
    Worksheets("Structuring").ChartObjects("Chart 3").Visible = True


End If


End Sub

Obviously, I'd like this to run everytime F74 on "Inputs" is changed from a 1 to a 0 or vice-versa.

Thanks.

 

[Domenic]

You can use the Worksheet_Change event. Right-click the sheet tab for your Inputs sheet, select View Code, and copy/paste the following code into the code module for the sheet...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> "$F$74" Then Exit Sub
    Application.EnableEvents = False
    If Target.Value = 1 Then
        Worksheets("Structuring").ChartObjects("Chart 3").Visible = False
    ElseIf Target.Value = 0 Then
        Worksheets("Structuring").ChartObjects("Chart 3").Visible = True
    End If
    Application.EnableEvents = True
End Sub

Hope this helps!

 

[VBAMePlease]

@Domenic Huge help - I couldn't use online resources to trigger the binary switch. Is there anyway to add-in an AND argument so that F76 must also equal "Probability-Sculpted"?

That would be the final trick that'd solve this for me...

The code above works splendidly though! Many thanks.

 

[Domenic]

You might need to amend the criteria for your ElseIf statement, depending on your actual requirement, but try...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$F$74" Or Target.Address = "$F$76" Then
        Application.EnableEvents = False
        If Me.Range("F74").Value = 1 And Me.Range("F76").Value = "Probability-Sculpted" Then
            Worksheets("Structuring").ChartObjects("Chart 3").Visible = False
        ElseIf Me.Range("F74").Value = 0 Then
            Worksheets("Structuring").ChartObjects("Chart 3").Visible = True
        End If
        Application.EnableEvents = True
    End If
End Sub

 

[VBAMePlease]

Here's my updated ElseIf statement:

Private Sub Worksheet_Change(ByVal Target As Range)    If Target.Address = "$F$74" Or Target.Address = "$F$76" Then
        Application.EnableEvents = False
        If Me.Range("F74").Value = 0 And Me.Range("F76").Value = "Probability-Sculpted" Then
            Worksheets("Structuring").ChartObjects("Chart 3").Visible = True
        ElseIf Me.Range("F74").Value = 1 And Me.Range("F76").Value <> "Probability-Sculpted" Then
            Worksheets("Structuring").ChartObjects("Chart 3").Visible = False
        End If
        Application.EnableEvents = True
    End If
End Sub

For whatever reason it won't toggle the chart for the "Probability-Sculpted" segment. If I toggle F74 to 1 and back to 0 and have F76 to something other than the Sculpted setting it won't show the chart, but when I then keep F74 as 0 and toggle to Sculpted and then toggle back to something else, it keeps the chart.

 

[VBAMePlease]

Disregard - got it to work with some adjusting.

Many thanks!