vba excel switch workbooks problem

[terriertrip]

A routine within ThisWorkbook opens a specific workbook and a routine runs from that workbook. In order for the routine to work correctly, ThisWorkbook has to be the ActiveWorkbook. Even if I set my workbooks properly, ThisWorkbook never activates after opening of the second workbook. Can someone tell me what I'm missing? Below is code within a ThisWorkbook module. ImportModules is a subroutine within the ActiveWorkbook module.

Please Login or Register  to view this content.

[dmcgov]

afaik/ when you run the routine from that workbook, it becomes the ThisWorkbook. you would first have to select the original workbook first and run some code from it before it reverts back to thisworkbook.

edit: must have read this wrong.

[Arkadi]

It is POSSIBLE that the "other" workbook is slow to come up, and that the wb.activate is running too early. You could try adding a short delay between opening the workbook and activating "This" workbook:

Please Login or Register  to view this content.

5 seconds is probably longer than needed, but I'll let you test that by reducing if 5 works.

[Fluff13]

Another possible problem, do you have any code in the other workbook that triggers on opening?

[terriertrip]

Arkadi: that doesn't seem to remedy
Fluff13: yes, I do as a matter of fact. Below is the code for the Wb2 (or ActiveWorkbook)

Please Login or Register  to view this content.

[Fluff13]

The line you have flagged is indeed the problem.
Change the name of the variable in one of the codes.

[terriertrip]

So I redim'd all wb references within Wb2 (ActiveWorkbook), but it still does not activate the wb (ThisWorkbook). Strangely when I insert a break on line Set swb = Workbooks.Open(FileName:=sPath, password:="password") and step through, ThisWorkbook does become the active workbook, but in runtime, ThisWorkbook is not activated. The timer doesn't seem to make a difference on activation or not.

Background: I'm trying to implement Ron de Bruin's method of updating module and form code using another workbook that hosts the updating macro code. I know I could create this process as an add-in but I'm not quite educated and don't have the time to learn how to do this and this was the workaround I came up with for the time-being.

[Fluff13]

What if you make this change

Please Login or Register  to view this content.

[terriertrip]

Arrghh. I'm sorry - that doesn't work either.

[Fluff13]

What does the msgbox say

Please Login or Register  to view this content.

[terriertrip]

It works! The msgbox tells me it is ThisWorkbook. Even using wb.Name works. Finally.

I initially had tried the msg "wtf", but I guess a more program friendly approach seems to have corrected the trajectory. Still curious as to why the operation is so buggy. Thank you!

[Fluff13]

Glad it's working & thanks for the feedback