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 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.
How would the macro know when the user is ready? What event says that the user is done?Originally Posted by xi603
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.
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.
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.
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.
Show the code for SetupVarMain.
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.
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.
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.
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.
here it is up to the SetupVarMain macro.
Please Login or Register to view this content.
Delete the red items. You cannot have duplicate declarations of the same variables.
Please Login or Register to view this content.
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.
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
i just called the SetupVarMain macro first and it seems to work. thanks for all your help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks