toggling a chart on and off

[VILLABILLA]

I have a situation involved with macro's and charts and maybe the use o
VBE would be a solution, I am unfamiliair with VBE. An example of m
spreadsheet is attached to this message with further description of m
question...

Thanks a lot in advance!

Robb

Attachment filename: example.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=45437

 

[VILLABILLA]

Please look at this attachement!

thnks

Attachment filename: try out.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=45439

 

[Don Guillett]

Add this line just before the with
ActiveChart.Parent.Name = "mychart"
Then you can delete with another button

Sub delchart()
ActiveSheet.ChartObjects("mychart").Cut
End Sub

 

[VILLABILLA]

Thanks a lot Don!

I just wouldn't know where to add the lines you mentioned..

My macro looks like this:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 25.2.2004 by Registered User
'

'
Range("B3:J3,B5:J5").Select
Range("B5").Activate
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceDat
Source:=Sheets("Sheet1").Range("B3:J3,B5:J5"), _
PlotBy:=xlRows
ActiveChart.SeriesCollection(1).Name = "=""Target"""
ActiveChart.SeriesCollection(2).Name = "=""Actuals"""
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "T vs A"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
ActiveWindow.Visible = False
Windows("try out.xls").Activate
Range("A3").Select
End Sub

Where should I add your lines to be able to delete the chart with
button?

Thanks a lot
Robb

 

[SOS]

VillaBilla,

Open your workbook and press Alt+F11 to take you to the VBA Editor. I
the left pane double click Module 1 and as seen below add the line

ActiveChart.Parent.Name = "mychart" to your code.

Then type out another Sub as shown below called Sub delchart.

Then back in your workbook add another button to your worksheet an
attach the macro delchart to it by right-clicking the new button an
choosing Assign Macro and selecting the macro delchart.

That should work

Regards

Seamus

__________________________________________________

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 25.2.2004 by Registered User
'

'
Range("B3:J3,B5:J5").Select
Range("B5").Activate
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceDat
Source:=Sheets("Sheet1").Range("B3:J3,B5:J5"), _
PlotBy:=xlRows
ActiveChart.SeriesCollection(1).Name = "=""Target"""
ActiveChart.SeriesCollection(2).Name = "=""Actuals"""
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"

ActiveChart.Parent.Name = "mychart"

With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "T vs A"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
ActiveWindow.Visible = False
Windows("try out.xls").Activate
Range("A3").Select
End Sub

___________________________________________________

Sub delchart()
ActiveSheet.ChartObjects("mychart").Activate
ActiveChart.ChartArea.Select
ActiveWindow.Visible = False
Selection.Delete
End Su

 

[VILLABILLA]

Seamus, I got it working now!

And it is of big help, thank you very much!

Robb