Hi All.

I'm self taught in VBA and usually get my answers searching through forums like this one. I'm finding the solution to this problem a little tough to find though so was looking for a bit of specific help.

My job is very repetitive. It usually involves receiving reports, processing the data and storing the outcome for somebody else to use later. I'm lazy. So, I write VBA code to do most of the work for me and save them in my personal macros workbook leaving me more time to... do other things.

So, this one report requires me to make some changes to it, upload to a global logging system, retreive a unique reference number from the logging system, add that to the report before then storing it. Sounds simple.

The report I get already has a lot of VBA code which takes care of the uploading to the logging system part and displays the resulting log reference. Who wrote this I've no idea but it is very complex and difficult to follow. Having this code changed in any way to suit my needs is not an option. I have however located the sub that starts the upload and am able to call that from my own macro. But because this upload code has to be used it means my own macro has to be done in two parts.

The first determines a unique description for the current report based on the number of these reports I have done that day (I store these in a local logfile), makes the other necessary changes to the report and then (call the) start (of) the upload to the global logging system. The report's own code takes over and after the upload userform displays the unique log reference, I manually add that to the report and then run my second macro. This stores the logging system reference along with the unique description I used and various other details into my local logfile, then saves the report with all the changes to be used by somebody else later.

I have automated all of this apart from where I need to enter the logging system reference number into the report which is the only reason I have to do this in two parts. Why have two click of the mouse when I could just have one? I'm very lazy. Besides, If I forget to run the second Macro then the next report I do will try to upload with the same description as the last one since the first macro uses the info the second macro stored into the local logfile to determine what the next description should be.

So, what ways can I resolve the problem? I don't know how much of the following is possible but these are my thought processes nontheless.

1: The logging reference I get from the upload userform must at some point be stored as a variable in order to display this on the userform. Can I somehow grab this variable so that I can use it once the upload code has finished excuting and my own code resumes? I'm not in any way able to have the report's own code changed to suit my needs so I'll need to have my code "go in and get it" so to speak or watch for it appearing on the userform rather than having the report's own code changed to save the reference in a cell somewhere or something like that.

2: Can I use my code to alter the report's own code before I execute the upload sub? This way I can change the code so that it stores the reference in a cell or something which I can then use once the upload code has finished executing.

3: As I'm writing this it occurs to me that once the upload code has finished executing, I could have my code display a userform requesting I input the reference I was given. This would reduce my number of macro's to one and would remove the possibilty of forgetting to run the second macro, but it still requires me to make a note of the reference and manually re-enter it. I'm really very lazy. I'd like to get this done in one hit if possible. It'd just be more elegant if I can get it to do it all by itself.

If any of you reading this far (I know you're currently sitting looking at your screen amazed at how I just managed to describe such an incredibly complex problem in so little text! lol sorry about that) have managed to decipher from my ramblings above what I'm looking to do and can guide me in the right direction or can think of anything I haven't please hit the reply button as soon as possible. This is a situation of life and death; I am currently doing more work than is absolutely necessary!

Thanks.