Opening and activating a file in VBA

[Justin16]

Hi,
I have an excel file that I am making that is a master file that calls and opens other files. My only problem is that when excel opens the new file it reactivates the master file instead of activating the file that was just opened. I want to be able to open the file, have the file become activated and then when I close the file I want the master file to be activated or open? Here is my present code, can anyone help me with this?

Please Login or Register  to view this content.

[Bernie Deitrick]

Are you running this from Excel or another application? You should be just using this to open the file in the current Excel instance

Please Login or Register  to view this content.

[Justin16]

I want to open the new file (Fedex) in a new instance of excel. Then when I close the "fedex" file, i want the "dispatch" userform to show. Is there any way to do this?

[alansidman]

Justin

Code Tags Added
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between

Please Login or Register  to view this content.

tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



(Because you are new to the forum, I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

[Bernie Deitrick]

Using two instances of Excel in code is unnecessary and make the code much more complicated, to the point that I don't want to continue in this thread. Is there a valid reason why you want Fedex opened in its own instance of Excel?

[Justin16]

The main reason is so the the macro won't make changes on the wrong workbook.

[Justin16]

Thanks for the changes, sorry about that.

[Justin16]

Your code works great, but I only want it to show the "Dispatch" userform after the "FedEx" workbook is closed. Can you help me with how to do that?

[Bernie Deitrick]

That is no reason if you properly create and use objects in your code, both in the source workbbook and in Fedex.xlsm. Typically, you create an Excel workbook object like this: In a standard codemodule:

Please Login or Register  to view this content.

Then in your un-named userform's codemodule

Please Login or Register  to view this content.

Then you can use the workbook object to direct your code to work on that workbook:

Please Login or Register  to view this content.

or other workbook objects that are available, like the workbook with the code:

Please Login or Register  to view this content.

So then the form could be shown using the activate event of the original workbook - in the codemodule of the ThisWorkbook object

Please Login or Register  to view this content.

[Justin16]

Thanks so much for writing up all that code. I copied the code into my userform, but it errors on:

Please Login or Register  to view this content.

I also tried putting this code in a module and it didn't seem to work either, can you give me any direction on this?

[Bernie Deitrick]

Did you insert a standard codemodule? Just use the Insert menu in the VBE...

[Justin16]

I inserted it into a new module like this:

Please Login or Register  to view this content.

But now it errors on this line of code:

Please Login or Register  to view this content.

[Bernie Deitrick]

Put just this at the top of a standard codemodule:

Please Login or Register  to view this content.

That is, remove the "Sub boolShowForm()" and the "End Sub"

Your declarations must be public to the project as intended, so they just go it alone at the top. Read more - and learn more - about variable scope in VBA at this page:

http://support.microsoft.com/kb/141693/en-us

[Justin16]

The code works now, but the "Fedex" file does not show. The "dispatch" userform shows and the "fedex" file can only be accessed once the "dispatch" userform is closed. Anyway to make the "fedex" file pop up automatically?

[Bernie Deitrick]

The dispatch userform should not show until the workbook's window is activated when Fedex is closed. Where did you put the event code?

[Justin16]

I put all the code into the userform button that calls "fedex".

Please Login or Register  to view this content.

This is probably where I am going wrong. Where should I put the code?

[Bernie Deitrick]

I gave clear directions in my post.

Change your sub to just this, as I originally showed:

Please Login or Register  to view this content.

Then put this code into the codemodule of the ThisWorkbook object: but first, read more about events and how to code them at

http://dmcritchie.mvps.org/excel/event.htm

and a much more advanced discussion at

http://www.cpearson.com/excel/Events.aspx

Please Login or Register  to view this content.

[Justin16]

Ok, thanks so much for your help! I should be able to take it from here. Thanks again!