+ Reply to Thread
Results 1 to 7 of 7

Chartobject in module 1 delete it in module 2

  1. #1
    Registered User
    Join Date
    12-29-2020
    Location
    Etten leur
    MS-Off Ver
    2016
    Posts
    19

    Chartobject in module 1 delete it in module 2

    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
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    01-12-2021
    Location
    Canada
    MS-Off Ver
    365
    Posts
    9

    Re: Chartobject in module 1 delete it in module 2

    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?

  3. #3
    Registered User
    Join Date
    12-29-2020
    Location
    Etten leur
    MS-Off Ver
    2016
    Posts
    19

    Re: Chartobject in module 1 delete it in module 2

    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

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,481

    Re: Chartobject in module 1 delete it in module 2

    Try naming the group when you create it.

    Please Login or Register  to view this content.
    Then in the other routine you can locate and delete using the name.
    Please Login or Register  to view this content.
    Cheers
    Andy
    www.andypope.info

  5. #5
    Registered User
    Join Date
    12-29-2020
    Location
    Etten leur
    MS-Off Ver
    2016
    Posts
    19

    Re: Chartobject in module 1 delete it in module 2

    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!

  6. #6
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,481

    Re: Chartobject in module 1 delete it in module 2

    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.

  7. #7
    Registered User
    Join Date
    12-29-2020
    Location
    Etten leur
    MS-Off Ver
    2016
    Posts
    19

    Re: Chartobject in module 1 delete it in module 2

    Quote Originally Posted by Andy Pope View Post
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. On Button Click Run A Module Then Wait Till Complete Then Run Another Module...
    By Ourkid123uk in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-13-2019, 02:16 PM
  2. [SOLVED] Extra Sheet not a Chart or Old Macro Module or Worksheet - Appears to be normal module???
    By Doc.AElstein in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 06-06-2017, 04:10 AM
  3. Compile error in hidden module: Module 1 when workbook opened on different machine!?
    By MathUKTeacher in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-06-2015, 04:38 PM
  4. [SOLVED] Code locks cells when inserted in sheet module but returns error in standard module
    By yoda66 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-07-2014, 07:39 AM
  5. Replies: 1
    Last Post: 08-30-2011, 02:23 AM
  6. how to access Sheet module, normal module, Worbook module to type code
    By alibaba in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-31-2009, 07:51 AM
  7. Replies: 1
    Last Post: 04-10-2005, 07:07 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1