Chartobject in module 1 delete it in module 2

[YFM_eng]

Hi All,

I'm working on a automated program for processing some production data.

I'm making a chart from scratch with chartobjects.add(top,left,width,height)
after that there are 2 shapes i'm adding to it, then I will group the 3 things. (all In module "Genereer_Aero_Graph_NEW")

In another module (Sheet_leeghalen) I want to delete the chart and shapes again so that the workbook goes back to a template style.

The chart and shapes won't delete, and now i'm a little stuck. (error = object variable or with block variable not set)

I've declared the variable Chartaero as Public, but no succes there.

Sorry I'm just beginning with VBA so I think I overlooked something.

Can anyone help me with this problem. I will attach the program with vba code.

Many thanks in advance!

BR Yuri

[clams]

Hi,

I am new to VBA but noticed something that you may have overlooked.

Within the module named "Sheet_leeghalen", did you declare the object variable Chartaero?

Also, the code which would set the variable 'Chartaero' has been commented out.

I believe this would cause an error.

Does that help?

[YFM_eng]

Hi clams,

Firts of all thanks for your reply, it's much appreciated!

My thought was that when you declare the variable as a public variable in the declaration section, the variable is available in other modules and procedures. But that doesn't seems to work.

Never the less I've tried to do as you proposed but it didn't work either.

The part in the module "sheet leeghalen" is 'set Chartaero = Nothing. It was a recommendation from a colleague to do it this way, but it hasn't got the desired effect. Which, in my opinion, is normal because then you set the variable to nothing so it has no chance to manipulate that object.

Now I'm looking for a way to declare the variable in the module "Sheet leeghalen". So I will do Dim Chartaero as Chartobject..so far so good... But I don't know how to set the variable...

I can try Set Chartaero= chartobject("...") But I don't know the name of the object..

I hope someone can give me a tip how to do that, or maybe I need another approach..

Thanks!

BR Yuri

[Andy Pope]

Try naming the group when you create it.

    Set Chartaero = shReg.ChartObjects.Add(Top:=265, Left:=1, Width:=700, Height:=287) ' definiering van de dimensies van de grafiek
    Set Fdev = shReg.Shapes.AddShape(msoShapeRectangle, 221.5, 300, 69.75, 21.75)
    Set Een = shReg.Shapes.AddShape(msoShapeRectangle, 295, 300, 380, 21.75)
    shReg.Shapes.Range(Array(Chartaero.Name, Fdev.Name, Een.Name)).Group

    shReg.Shapes(shReg.Shapes.Count).Name = "MYCHART"

Then in the other routine you can locate and delete using the name.

'Grafiek weghalen
   'On Error Resume Next

    shReg.Shapes("MYCHART").Delete
   
'  Fdev.Delete
'  Een.Delete

[YFM_eng]

Hi Andy,

Thanks a lot ! You're post was very helpfull. When I named the shapes like you suggested, the shapes (two green rectangles in top of chart) are deleted. However the chart still remaines.

You've pushed me in the right direction though, because with the code shReg.Chartobjects.delete I've erased all the charts in the worksheet.

It's a solution for now, since the chart was the only chart in the sheet. Thanks!!

Still i'm a bit curious why the public variable cannot be altered or deleted in the other module, but maybe I will learn when I get a bit more familiar with vba programming .. but it works fine for now!

Cheers Yuri!

[Andy Pope]

The code deletes a grouped shape. It does not delete individual shapes or chartobjects.

When I test you original code it worked as described.
A public object will retain it's reference unless the code is reset. This can be caused by an error, resetting VBE via the stop button or using the END command.

[YFM_eng]

The code deletes a grouped shape. It does not delete individual shapes or chartobjects.

When I test you original code it worked as described.
A public object will retain it's reference unless the code is reset. This can be caused by an error, resetting VBE via the stop button or using the END command.

Ok andy. Thanks for the information.