+ Reply to Thread
Results 1 to 17 of 17

resume a macro after user makes some changes to file

  1. #1
    Forum Contributor
    Join Date
    03-31-2017
    Location
    usa
    MS-Off Ver
    2013
    Posts
    104

    resume a macro after user makes some changes to file

    hi, I am running a macro that has some call subs in it. however, after a few are run the user has to do some manual things to the file, so I want the macro to stop then and when the user is ready to proceed from where it left off. is there a relatively simple way to do this? in the maro below it should stop after the refresh pivots call and go to PublishFile when the user is ready. thx.

    Please Login or Register  to view this content.

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    19,679

    Re: resume a macro after user makes some changes to file

    How is this macro invoked?

    What actions does the user need to take, and what determines that they are ready to continue?

    The easiest way to do this is to split this into two Subs, and give the user a button to press when he's ready to run the second Sub.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,576

    Re: resume a macro after user makes some changes to file

    Quote Originally Posted by xi603 View Post
    hi, I am running a macro that has some call subs in it. however, after a few are run the user has to do some manual things to the file, so I want the macro to stop then and when the user is ready to proceed from where it left off. is there a relatively simple way to do this? in the maro below it should stop after the refresh pivots call and go to PublishFile when the user is ready. thx.

    Please Login or Register  to view this content.
    How would the macro know when the user is ready? What event says that the user is done?

    Ther more common practice is to have two macros, where the first macro refreshes and just ends. Then the user does something and presses a button or something to to publish.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  4. #4
    Forum Contributor
    Join Date
    03-31-2017
    Location
    usa
    MS-Off Ver
    2013
    Posts
    104

    Re: resume a macro after user makes some changes to file

    i tried to run PublishFile macro separately but ran into some problems with subscripts out of range. the only way I could fix it was to show the entire path and filenames again, which I was trying to avoid so that those things only stayed in one place. I've also had trouble with public variable so was trying to avoid that too.

    the user has to add some data to the file depending on what errors the formulas generate. then the file is ready to be published.

  5. #5
    Forum Contributor
    Join Date
    03-31-2017
    Location
    usa
    MS-Off Ver
    2013
    Posts
    104

    Re: resume a macro after user makes some changes to file

    Because of the out of range error I though I could just do the Call SetupVarMain, where the path and filenames are set up initially, inside the PublishFile macro but that didn't seem to work either.

  6. #6
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,576

    Re: resume a macro after user makes some changes to file

    Show the code for SetupVarMain.

  7. #7
    Forum Contributor
    Join Date
    03-31-2017
    Location
    usa
    MS-Off Ver
    2013
    Posts
    104

    Re: resume a macro after user makes some changes to file

    here it is. I'd like to be able to just call this in the PublishFile macro to avoid the out of range errors instead of having to put the pathname and filenames again, which does work if I do it that way.

    Please Login or Register  to view this content.

  8. #8
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,576

    Re: resume a macro after user makes some changes to file

    Declare the variables you want to share between procedures as Public and at the very top of the code module.

    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    03-31-2017
    Location
    usa
    MS-Off Ver
    2013
    Posts
    104

    Re: resume a macro after user makes some changes to file

    In my original post example above, would these public variables go inside the RunMainFile macro or on top of that even?

  10. #10
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,576

    Re: resume a macro after user makes some changes to file

    Quote Originally Posted by xi603 View Post
    In my original post example above, would these public variables go inside the RunMainFile macro or on top of that even?
    On top of that even. Outside of any procedure at the very top of the code module.

  11. #11
    Forum Contributor
    Join Date
    03-31-2017
    Location
    usa
    MS-Off Ver
    2013
    Posts
    104

    Re: resume a macro after user makes some changes to file

    I got a compile error that says ambiguous name detected myFilePath

  12. #12
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,576

    Re: resume a macro after user makes some changes to file

    Quote Originally Posted by xi603 View Post
    I got a compile error that says ambiguous name detected myFilePath
    Well you'll have to post all your code then. And tell me what line of code throws the error.

  13. #13
    Forum Contributor
    Join Date
    03-31-2017
    Location
    usa
    MS-Off Ver
    2013
    Posts
    104

    Re: resume a macro after user makes some changes to file

    here it is up to the SetupVarMain macro.

    Please Login or Register  to view this content.

  14. #14
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,576

    Re: resume a macro after user makes some changes to file

    Delete the red items. You cannot have duplicate declarations of the same variables.

    Please Login or Register  to view this content.

  15. #15
    Forum Contributor
    Join Date
    03-31-2017
    Location
    usa
    MS-Off Ver
    2013
    Posts
    104

    Re: resume a macro after user makes some changes to file

    That fixed the compile error but one sub is still not recognizing the global variable. will have to work on this later, but now I know the general area of the problem.

  16. #16
    Forum Contributor
    Join Date
    03-31-2017
    Location
    usa
    MS-Off Ver
    2013
    Posts
    104

    Re: resume a macro after user makes some changes to file

    i have 2 relevant sheets open; the small file from where i run the macro and the large main file. even though the variable mainFile is public when i try to go to the main file and the relevant sheet when i am in the macro file using the commands below i get an error. if i were already in the mainFile and on that sheet it would work.

    Windows(mainFile).Activate
    Worksheets("PTN Detail").Activate

  17. #17
    Forum Contributor
    Join Date
    03-31-2017
    Location
    usa
    MS-Off Ver
    2013
    Posts
    104

    Re: resume a macro after user makes some changes to file

    i just called the SetupVarMain macro first and it seems to work. thanks for all your help.

+ 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. Somehow, my macro makes my file go from 8mb to 60mb!! AAAahhhh Help!!
    By dgreelis in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 10-07-2016, 05:02 PM
  2. Macro for Exporting to PDF makes file too large
    By oiltech999 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-13-2016, 04:58 PM
  3. [SOLVED] Pausing A Macro (allow user to extract data) aAnd Resume Macro
    By jbumps in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-15-2013, 01:59 PM
  4. [SOLVED] Gray out cells until user makes input
    By jbeeball in forum Excel General
    Replies: 3
    Last Post: 05-21-2012, 12:48 PM
  5. Save workbook when user makes changes
    By James12 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-25-2009, 03:19 PM
  6. Pause macro user selection, then resume
    By Linda in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-21-2005, 01:05 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