Macro problem 1

[Chris559]

Using Excel 2000 I have recorded a macro to display a chart using data from the spreadsheet. No problem - all works OK. However I want to have another macro to remove the chart. I have tried using "Delete" and also "Clear" and then "All". Both come up with "Run time error 91". Trying to debug, Visual Basic comes up with

ActiveWindow.Visible=False
Selection.Delete
EndSub

I lost all the worksheet the first time - thank goodness I had a backup.

Any help would be appreciated

Chris559

 

[Zathras]

This macro will remove all charts from the current worksheet:

Option Explicit

Sub DeleteAllCharts()
Dim i As Integer
With ActiveSheet
For i = .ChartObjects.Count To 1 Step -1

  ' MsgBox for test/debug only.
  ' Remove for production.

  MsgBox .ChartObjects(i).Chart.Name
  .ChartObjects(i).Delete
Next i
End With
End Sub

 

[Chris559]

Thanks Zathras

Still doesn't like it!! Again the whole workbook cleared although the file still exists but comes up as Book 1.

On the debug, the line starting "For i = ......" comes up highlighted in yellow

What am I doing wrong???

Chris559

 

[Draig]

Hi Chris559

If you are using the macro suggested by Zathras, how are you launching it? A button? Menu command?

Barborne
Worcester
UK

 

[Zathras]

Are you putting your chart(s) on a separate worksheet?

If so, that would probably cause you to lose things since by making the active window invisible, some other window becomes the active window and that is the one you are deleting.

I had assumed that you were putting your chart on an existing worksheet, which is the default setting.

Where are you putting your charts?
Where are you pasting the macro code?
What else haven't you told us?

 

[Chris559]

Thanks Zathras/Barborne

I am launching the macro with a button in an existing worksheet. I named the button DeleteAllCharts and pasted your macro into it in Visual Basic

Does that help?

Many thanks

Chris559

 

[Zathras]

I just tried that and it worked correctly.

There's something you're not telling us. Please tell us step by step what is happening. You could also post the macro code from the module. Select all of the text, copy and paste into a reply here.

If you are using the code as posted above, you should see a message box displaying the name of each chart before it is deleted. Are you seeing any messages?

 

[Chris559]

Right. I now seem to have it!! This is what I have in VB -

Option Explicit

Sub DeleteAllCharts()
Dim i As Integer
With ActiveSheet
For i = .ChartObjects.Count To 1 Step -1
' MsgBox for test/debug only.
' Remove for production.
MsgBox .ChartObjects(i).Chart.Name
.ChartObjects(i).Delete
Next i
End With
End Sub

Where I went wrong was in leaving a sentence saying when I had recorded it.

Once it has run I get an MS Excel message box and I click on OK to delete the chart. Each time I do so the chart number goes up by one.

Thanks for your patience and help!!

Chris559