| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Automated show and hide chart
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 |
|
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
Automated show and hide chart
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 -- John Mansfield http://www.cellmatrix.net "soma" wrote: 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 |
|
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
Automated show and hide chart
Thanks, let me try that. i will get back if i have more questions.
Regards, "John Mansfield" wrote: 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 -- John Mansfield http://www.cellmatrix.net "soma" wrote: 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 |
|
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
Automated show and hide chart
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 "soma" wrote: Thanks, let me try that. i will get back if i have more questions. Regards, "John Mansfield" wrote: 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 -- John Mansfield http://www.cellmatrix.net "soma" wrote: 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 |
|
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
Automated show and hide chart
Yes, both macros will trigger the instructions when the value of the cell
changes. -- John Mansfield http://www.cellmatrix.net "soma" wrote: 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 "soma" wrote: Thanks, let me try that. i will get back if i have more questions. Regards, "John Mansfield" wrote: 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 -- John Mansfield http://www.cellmatrix.net "soma" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Show and hide a embedded chart? | Charts and Charting in Excel | |||
| How can I show/hide plots in a chart? | Charts and Charting in Excel | |||
| Want to Hide columns in spreadsheet but NOT hide data in chart. | Charts and Charting in Excel | |||
| Chart Legend Items: hide/show | Charts and Charting in Excel | |||
| hide column but show chart | Charts and Charting in Excel | |||
Linear Mode
