Automated show and hide chart

  • Thread starter Thread starter soma
  • Start date Start date
S

soma

i am working on a spreadsheet that generates a grpah for me. sometime i dont
need that graph to show up in the spreadsheet rater a message instead of
graph.
is there any macro that can help me do that automatically. the message will
be triggered by a value in a cell and then should hide the graph and display
the message. at the moment i do the same manually. but i have to do it for
over 300 sheets so if i can automate it somehow that will help a lot.
any help.
Thanks
 
You could try experimenting with these two examples:

The macro below will show and hide an embedded chart and message based on a
calculated value + or - 50 in cell B2.

Option Explicit

Private Sub Worksheet_Calculate()

Dim Msg As String
Dim Rng As Range

Msg = "my message"
Set Rng = ActiveSheet.Range("B3")

If ActiveSheet.Range("B2").Value > 50 Then
Worksheets("Sheet1").Shapes("Chart 1").Visible = True
Rng.Value = " "
Else
Worksheets("Sheet1").Shapes("Chart 1").Visible = False
Rng.Value = Msg
End If

End Sub

This will do the same only with a direct input o cell B2.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim Msg As String
Dim Rng As Range

Msg = "my message"
Set Rng = ActiveSheet.Range("B3")

If Target.Address = ActiveSheet.Range("B2").Address Then
Application.EnableEvents = False
If Target.Value > 50 Then
Worksheets("Sheet1").Shapes("Chart 1").Visible = True
Rng.Value = " "
Else
Worksheets("Sheet1").Shapes("Chart 1").Visible = False
Rng.Value = Msg
End If
Application.EnableEvents = True
End If

End Sub
 
Worked like a charm. just one more question is it possible to triger the
macro as soon as i change the value in the cell. for example if i use B3 > 50
(as per macro) it shoud automatically do what it suppose to do?
Thanks
 
Yes, both macros will trigger the instructions when the value of the cell
changes.
 
Back
Top