Deleting Charts

[JLynn]

Hello,

I am having a strange error when I try to delete my old charts in my excel workbook. Below is the code I am using to try to get the Workbooks property to work:

Sub Selecting_Charts()
Workbooks("Op 70.xls").Activate
Sheets("Charts").Select
Workbooks("Op 70.xls").Charts.Select           'Error here!     
End Sub

I keep getting the error "Method 'Select' of object 'Sheets' failed" when trying to run the code. I don't know anything more to try to get my program to select that workbook. I also tried the 'ThisWorkbook' property but that produced the same error.

Please help me on this issue. I greatly appreciate it.

Thanks again,

Jordan

[JLynn]

I really need help with this small section of code to complete a project for work. Please, I really need some help!

Thanks,

Jordan

[Jupiter 2]

Excel Charts are in the Chart object and not Sheets object, in Excel 2003 and over

Sub Selecting_Charts()
Workbooks("Op 70.xls").Activate
'Sheets("Charts").Select
Charts.delete, true
'Workbooks("Op 70.xls").Charts.Select           'Error here!     
End Sub

[JLynn]

Charts.Delete , True

That code does not want to work. I get the error 'Wrong number of arguments or invalid property assignment' when I add that code.

Thanks for your help, though.

Jordan

[Jupiter 2]

This example deletes every chart sheet in the active workbook.

ActiveWorkbook.Charts.Delete

[JLynn]

The code still will not work, even though it should.
The following is the code I currently have:

Sub Selecting_Charts()
Workbooks("Op 70.xls").Activate
ActiveWorkbook.Charts.Delete
End Sub

I get the following error: "Method 'Delete' of object 'Sheets' failed".
I am very baffled by this and have no idea why my charts won't delete because the code you gave me should work because I have tried it in the past.

Thanks again for your help.

Jordan

[Jupiter 2]

You are probably using Excel 2007. I've only got up to Excel 2003.

My last message came from the Excel Help file.

If you are using Excel 2007, you need to ask in the VB.Net section, or Excel Secistion

[JLynn]

I am using Excel 2003, not 2007.

[Jupiter 2]

Are you using a visual basic form to initiate the macro or is the macro inserted into a worksheet?

Perhaps you could just Clear formats rather than deleting the chart sheet.

Is the chart inserted as an onject into the excel worksheet or is it a separate Chart sheet?

[Jupiter 2]

I inserted a chart sheet by right-click the sheet tab.

I created a userform Added a button. Ran the userform and it deleted the Chart Sheet. There was a confirm delete dialog.

Private Sub cmdDelChartSheet_Click()
ActiveWorkbook.Charts.Delete

End Sub

[JLynn]

I'm amazed that my code still will not work. It gets tripped up every time on the 'ActiveWorkbook.Charts.Delete' line. I don't know if there is some internal glitch in my version of VB or Excel 2003 or what, but the code should work and it doesn't.

Thanks anyway. I do appreciate it.

[Jupiter 2]

Where have you placed the code?

How do you initiate the macro?

[JLynn]

Before I placed the sub in a module with several other subs. Then I created a 'Click' sub for the button on the appropriate worksheet. I initiated the macro and placed the macro no differently than I had previously. I have charts in the correct workbook, so recognizing the charts should not be an issue, but it is.

[Jupiter 2]

You said "Workboooks.activate" Why? Are the subs in a different workbook? If so, use workbook(whatever).select

Is the button on the Chart sheet? If so, put it on a worksheet.

Better to use a userform from the Visual Basic. Otherwise assign the macro to a toolbar button.

Is the Chart on its own Chart Sheet? Or is it a chart on a worksheet?

I'm off to bed now. I'm in Australia and the time is 2:50am

[JLynn]

Each time the real macro is run, new charts are created, hence the need to delete the old charts to keep the program running efficiently. The chart is inserted as a new object each time on the existing sheet (a new sheet is not created each time).

[Jupiter 2]

Sub Selecting_Charts()
Workbooks("Op 70.xls").Activate
Sheets("Charts").Select
Workbooks("Op 70.xls").Charts.Select 
          'Error here!    
 End Sub

For starters, a sub must be marked either Private or Public.
"Private Sub Selecting_Charts()"
"Public Sub Selecting_Charts()"

Private means it cannot be seen in the Run Macro Box.

Assuming there is only one workbook open and the macro is in this one workbook, and assuming the macro is attached to an on-sheet button object on a separate worksheet that is not the Chart sheet,

1) you do not need to delete the chart sheet. Just change the settings of the old chart to fit the new settings for the new chart.

2) Right-click the onsheet object button to view Properties.
Rename the CommandButton Name to cmdDelChart_01
Close Properties Dialog.
Right-click the onsheet object button to View Code

Private Sub cmdDelChart_01_Click()
ActiveWorkbook.Charts.Delete
End Sub

This worked for me, as well.

By not providing proper information, you may not get a proper result.

[JLynn]

I have already done all that stuff and my code still will not work. I think there might be some glitch with my excel.

[Jupiter 2]

You say you have "done all that stuff" but you still haven't told me where the macro is located.

The macro doesn't work if there are no Chart sheets.
Chart Sheet is not a normal sheet renamed "Charts", is it?

[JLynn]

The button initiating the macro is on the "Main" worksheet. The charts are on the "Charts" worksheet. There are charts on the "Charts" worksheet and the button on the "Main" worksheet calls the macro to delete all the charts in the workbook (all the charts are on the "Charts" worksheet).

[Jupiter 2]

Is it a worksheet "Sheet1" renamed to "Charts" ? If so, then it is not a CHART sheet but a normal worsheet.

Sheets("Charts").delete

This works for me.

[JLynn]

I'm not looking to delete my worksheet named "Charts". I'm looking to delete the charts on my worksheet named "Charts".

[Jupiter 2]

ActiveSheet.ChartObjects("Charts").Activate
    ActiveChart.ChartArea.Select
    ActiveWindow.Visible = False
    Selection.Delete

[JLynn]

Sorry, but this doesn't want to work either.

[Jupiter 2]

ActiveSheet.ChartObjects.Delete

This is all you need inside your Sub routine.