+ Reply to Thread
Results 1 to 5 of 5

Error when calling two similar subroutines at the same time

  1. #1
    Registered User
    Join Date
    10-04-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    19

    Error when calling two similar subroutines at the same time

    Hi,

    I have two subroutines that i have written which are extremely similar and are tasked with creating charts of two different types. When called individually they work fine. However when i try and call both under one command button i get the error "method 'columns' of object '_global' failed".

    The code seems to stop at the point in the second sub when it is asked to define the size of the second chart. I think this is telling me that VBA is getting confused with both codes essentially trying to define two different chart sizes for what it assumes is one set of chart objects.

    Here is the code below for the two subs:

    Please Login or Register  to view this content.
    I'm then trying to call these two subs with one command button using this code:

    Please Login or Register  to view this content.
    I think lack of experience is the real error here so any help would be very much appreciated!

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,626

    Re: Error when calling two similar subroutines at the same time

    Where exactly are you getting the error in the 2nd sub?

    Is it on the line where you add the chart?
    Please Login or Register  to view this content.
    Where are you trying to create the charts?

    In both subs you are using ActiveSheet but if the first sub was to create the chart on a chart sheet that would be
    the active sheet when the 2nd sub runs.

    If the active sheet is a chart sheet it could cause problems.

    For example, a chart sheet doesn't have columns.

    Can you attach a sample workbook?
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    10-04-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: Error when calling two similar subroutines at the same time

    That's exactly where the code stops, or more specifically on the second line where columns are used to define the Left value.

    I am trying to create both types of chart on one sheet which is just a normal spreadsheet. The activesheet is referring to the sheet where the code gets the chart source data from (the activesheet is created from a template and has a name which is created by the user).

    I'm now thinking that if the second sub needs to be called directly after the first then somewhere in the code i need to declare that i would like it to go back to the activesheet rather than run from the sheet the charts are being created on?? I can't upload anything at the moment because the servers at the office don't let us!

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,626

    Re: Error when calling two similar subroutines at the same time

    I can actually run the code up to this point in the 2nd sub, so perhaps what I suggested isn't the problem.
    Please Login or Register  to view this content.
    Of course since I don't have any data I don't get proper charts.

    You don't need to declare you want it to go back to the active sheet, what you should do is add explicit worksheet references wherever they are needed.
    Last edited by Norie; 01-09-2013 at 12:11 PM.

  5. #5
    Registered User
    Join Date
    10-04-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: Error when calling two similar subroutines at the same time

    I took another look at this last night and managed to solve it! As suspected, the code was getting confused with what sheet to pull the data from and also thought there were two separate commands the size of the chart. So i found a macro that i put into the sheet that selects the previously selected sheet. With the way the rest of the code runs it seems to work:

    This goes into thisworkbook:

    Please Login or Register  to view this content.
    and this goes into a module:

    Please Login or Register  to view this content.

    I then called this sub within the first sub and it seems to sort things out!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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