Undesired change of active workbook

Hi Keith,

Post the problematic code.


---
Regards,
Norman



"keithb" <k31thb@yahoo.com> wrote in message
news:Oyv$2qfoFHA.3316@tk2msftngp13.phx.gbl...
> 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
>

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" <normanjones@whereforartthou.com> wrote in message
news:eRKevpgoFHA.3912@TK2MSFTNGP10.phx.gbl...
> Hi Keith,
>
> Post the problematic code.
>
>
> ---
> Regards,
> Norman
>
>
>
> "keithb" <k31thb@yahoo.com> wrote in message
> news:Oyv$2qfoFHA.3316@tk2msftngp13.phx.gbl...
>> 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
>>
>
>

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" <k31thb@yahoo.com> wrote in message
news:uaIQvkhoFHA.4028@TK2MSFTNGP10.phx.gbl...
> 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" <normanjones@whereforartthou.com> wrote in message
> news:eRKevpgoFHA.3912@TK2MSFTNGP10.phx.gbl...
>> Hi Keith,
>>
>> Post the problematic code.
>>
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "keithb" <k31thb@yahoo.com> wrote in message
>> news:Oyv$2qfoFHA.3316@tk2msftngp13.phx.gbl...
>>> 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
>>>
>>
>>
>
>