Undesired change of active workbook

[keithb]

An Application has 3 open workbooks; wb1, wb2, and wb3.

With wb2 active, a selection is made from a cell dropdown list that
references a list in wb3. After the dropdown list operation, wb1 is active
instead of wb2 which originated the operation. What is the best way to
insure that wb2 stays active?

Thanks,

Keith

 

[Norman Jones]

Hi Keith,

Post the problematic code.

 

[keithb]

Thanks for responding. Actually, there is no code associated with this
problem; however, I want to write code that will prevent or solve the
problem. Here is more detail:

Window 3 has a Sheet1 of a workbook named "Definition.xls.
Window 2 has a Sheet1 of a workbook named "WSDL.xls
Window 1 has a Sheet1 of a workbook named "BusSvc.xls

In Rows 1 to 22 of column 14 on WSDL.xls contain a series of in-cell
drop-down list validations each with a list source named "=Domain" The name
"=Domain" is defined as, ='BusSvc.xls'!xpath. In BusSvc.xls, the name
"xpath" is defined as =OFFSET(Sheet1!$E$1,1,0,COUNTA(Sheet1!$E:$E)).

Everything works as expected when only WSDL.xls and BusSvc.xls are open;
however undesired behavior occurs if more worksheets than 2 are open.
Specifically, what happens in the example cited above is:

With WSDL.xls active, a user selects a value in Column 14 using a drop-down
list. The selected value (taken from BusSvc.xls) is correctly inserted into
the cell on WSDL.xls; however after the operation, the active workbook is no
longer WSDL.xls. Instead, Defnition.xls is active. In the example cited
above, Definition.xls was opened first. When the test is performed with
varying numbers of open workbooks open, it is always the workbook that was
opened first that becomes active after the drop-down list operation
completes.

Since this application must work with varying numbers of open workbooks, I
am looking for a VBA solution that allows detecting and saving a reference
to the active workbook/worksheet just prior to the drop-down list operation
and allows reactivating the desired workbook prior to returning control to
the user.

Thanks for any help or suggestions that you can offer.

Keith

 

[Norman Jones]

Hi Keith,

I have not sought fully to understand your scenario, but I would suggest
that, at the outset, you assign the initial Worbook/Sheet to object variable
and then use the variable to return to the desired book.

something like:

Dim WB as Workbook
Dim SH as Worksheet

Set WB = Workbooks("WSDL.xls")
Set SH = WB.Sheets("Sheet1")

' Your operation code

SH.Activate