Undesired change of active workbook
 

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

Undesired change of active workbook
 

Hi Keith,

Post the problematic code.


---
Regards,
Norman



"keithb" wrote in message
...
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

Undesired change of active workbook
 

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" wrote in message
...
Hi Keith,

Post the problematic code.


---
Regards,
Norman



"keithb" wrote in message
...
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

Undesired change of active workbook
 

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


---
Regards,
Norman



"keithb" wrote in message
...
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" wrote in message
...
Hi Keith,

Post the problematic code.


---
Regards,
Norman



"keithb" wrote in message
...
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