Issue while setting Excel active window caption

Hi,

We have a Excel COM Addin which has a button. On button click, we are setting the caption of the active window
and then perform workbook.Activate. The issue is after setting the application.ActiveWindow.caption, subsequent calls  of ActiveWorkbook.Activate event is activating the first sheet of excel no matter which sheet is on focus. That means if excel workbook has 3 sheets and currently 3rd sheet is active, when I click on the button first sheet is getting activated.

Has anyone seen this kind of issue before. Is there any solution or workaround for this issue.

Thanks in advance
pkoivulaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RayData AnalystCommented:
Working on a copy of your excel file (to protect the original), can you delete the "first sheet" then use your button that runs your "application.ActiveWindow.caption" macro and let us know what happens?

I'm guessing that something in that code, or other code, actually tells it to select the "first sheet".  If it is deleted and something is referencing it, then it might cause an error and that would tell us for certain that some code is causing your issue.

If that fails, can you post the workbook?
0
pkoivulaAuthor Commented:
Hi Ray,

Tried by deleting the first sheet, but facing same issue. This issue can be replicated using VBA macros also. Please set ActiveWindow.Caption to some text and then run ActiveWorkbook.Activate in the immediate window of excel VBA developer window.
Please find attached the excel workbook.

Thanks
Book1.xlsx
0
[ fanpages ]IT Services ConsultantCommented:
[ https://msdn.microsoft.com/EN-US/library/office/ff821837.aspx ]

"Workbook.Activate Method (Excel)

Activates the first window associated with the workbook."

Note: Not necessarily the first worksheet, of the first window, but that does appear to be the case.

If you select the second (or third) worksheet, do not change the ActiveWindow Caption property, & execute ActiveWorkbook.Activate; same result... the first worksheet is activated.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

pkoivulaAuthor Commented:
Just doing ActiveWorkbook.Activate is not activating first worksheet. Only after changing ActiveWindow.Caption and running ActiveWorkbook.Activate is making the first worksheet to get activated.

Please check with excel and execute these two statements in the immediate window of VBA developer window of excel.
0
RayData AnalystCommented:
Dang it!  I hate when I miss something like that.  My GUESS is that changing the caption essentially resets the workbook somehow.

My workaround would be to set a variable with the activeworkbook name or index at the beginning of the code then after ActiveWorkbook.Activate at the end, activate the the original worksheet.  Example below.

Dim xSName As Worksheet
Set xSName = ActiveSheet

'Run your existing code here

xSName.Activate

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
[ fanpages ]IT Services ConsultantCommented:
Just doing ActiveWorkbook.Activate is not activating first worksheet. Only after changing ActiveWindow.Caption and running ActiveWorkbook.Activate is making the first worksheet to get activated.

Please check with excel and execute these two statements in the immediate window of VBA developer window of excel.

I did; with both MS-Excel 2003, & MS-Excel 2013, before I posted above.

However, belatedly thinking about it now, I do have an Add-In running in both applications that changes the Window Caption, so I guess that may have interfered with the results! :)

As Ray suggested, yes, I retain an object variable pointing to the Active Worksheet of the Active Workbook, & also a second object pointing to the Active Workbook, & reinstate both in Workbook, then Worksheet, order when I have any code executing that is likely to be affected by any other process, or any user at the PC, selecting another workbook, or a worksheet other than that activated within the code.
0
pkoivulaAuthor Commented:
Is there anyway to achieve the same results through excel settings without code changes?
0
RayData AnalystCommented:
You can disable your Add-In, but other than that I do not believe so.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.