excel 2000 workbook.activate not 100% reliable with alt+tab

[Gavin Frayne]

hi folks,

i've got a bit of a wriggler here. any help would be appreciated.

in excel 2000 each workbook is opened in its own excel instance i.e.. i open
file1.xls and file2.xls and i can see both on the taskbar and can use
alt+tab to switch between them.

i have written some custom menus in file1.xls which are added in the
workbook_activate event and removed in the workbook_deactivate event. thus,
in theory, when i switch between file1 and file2, the menus should appear in
file1 and dissapear when i return to file2. this is however not the case. if
i use the mouse to click on the instances in the task bar, there is no
problem. however, if i use alt+tab, some of the time the menus are
activated, and some of the time they aren't.

i can't seem to find anything that this problem could be connected to. even
if i use the following simple code in file1:

Private Sub Workbook_Activate()
MsgBox "workbook is active"
End Sub

and have file2 is a new document, i still get the problem. i switch with
alt+tab from file1 to file2, and when i switch back, the msgbox is not
displayed.

i have noticed in the news groups that there are a number of people who have
built custom menus. do you also have this problem, or haven't you noticed
it, or doesn't it bother you? i like to work with the keyboard and it would
be great if i could get this working. i have already tried to using the
sheet_activate and window_activate events but they have same effect. does
anyone know which event i need to use in order to _always_ trigger my macro
when i move between windows or files?

regards,
gavin

 

[Dave Peterson]

If you're really opening multiple instances of excel, that's your problem.

Open those two workbooks the normal way. Then in one of the applications, click
on window (on the menubar). Do you see both workbooks at the bottom of that
list? If you don't, then you really have two instances running.

And those two instances are separate and distinct. They don't talk to each
other. Just like leaving excel and going to notepad/word/solitaire, then coming
back to excel won't cause that workbook_active code to fire, neither will going
to another instance of excel.

I think you want to open both files in one instance of excel. But you can turn
on windows in taskbar so that it looks like it does now. (Tools|Options|view
tab|windows in taskbar).

Then swapping between different workbooks in the same instance of excel will do
what you want.

If you click on an excel icon and it starts a new instance (check under window),
you should be able to change this behavior by either:

Close Excel and
Start|Run
excel.exe /regserver

The /regserver stuff resets the windows registry to excel's factory defaults.

If that doesn't work, try:
Try Tools|Options|General|Ignore other applications (uncheck it)

 

[Tom Ogilvy]

You are correct that using alt-tab does not fire the workbook activate event
whether the file1.xls and file2.xls are in the same instance of excel or in
separate instances of excel. Your observation that the menus are sometimes
changed is probably due to some other action and not due to Alt-tab. (I
could be wrong, but I never saw the events fired).

Dave Peterson said:

Then swapping between different workbooks in the same instance of excel will do
what you want.

Using Excel 2000 under Windows XP, I found this not to be true when Alt-Tab
was used to switch between windows. Perhaps Dave had a different
experience.


I suspect this is a bug. In separate instances of Excel, the current
behavior makes sense, but in the same instance either Alt+Tab shouldn't work
for child windows or it should fire the activate event.

Regards,
Tom Ogilvy

 

[Dave Peterson]

Nope. I was just wrong.

I think I was thinking of workbook_activate.

 

[Dave Peterson]

I don't have xl2k to try it, but could you use "Workbook_SheetSelectionChange".

You might be one step behind, though.

Or add a check to your macros based on something on the good workbook:

If Not (LCase(ActiveWorkbook.Name) Like "*important*") Then
MsgBox "not on this worksheet"
'even reset the toolbar here.
Exit Sub
End If

 

[Gavin Frayne]

Hi Dave,

You're right, with Workbook_SheetSelectionChange I'm always a step behind.

Your other idea is a good one, but unfortunately I want the workbook with
the macros to be open and the user to then be able to work in their own
excel files (most without macros) without my menu's interfering. My point
is, I only have macro control over the excel file, where I want the menu's
to appear. I can't include a check in every excel file on the server to see
if that workbook should receive the menu's or not.

Is this what you meant, or did I misunderstand something?

Regards,
Gavin




I don't have xl2k to try it, but could you use
"Workbook_SheetSelectionChange".

You might be one step behind, though.

Or add a check to your macros based on something on the good workbook:

If Not (LCase(ActiveWorkbook.Name) Like "*important*") Then
MsgBox "not on this worksheet"
'even reset the toolbar here.
Exit Sub
End If

 

[Dave Peterson]

Coincidentally, Stephen Bullen posted a response to a very similar question:
http://groups.google.com/[email protected]
(when I searched, google hadn't grabbed all the thread. Look for "Two AddIn
Questions" in the subject in your newsreader.)

But it does rely on putting something in the workbook that can be checked.

 

[Gavin Frayne]

Hi Dave,

I really appreciate your help, but I'm not sure if I understand how your
suggestion can help me. Let me try to be as clear as possible about what I
wish to achieve:

File 1: c:\templates\admin\invoice.xls
File 2: c:\user\anything.xls

When I open invoice.xls I want my menu's to appear. Among others, my menu's
overwrite the standard save, save as and paste commands.

Now, while I have invoice.xls open on my desktop, I want to be able to open
c:\user\anything.xls and the menu's must not appear.
This work perfectly when I use the mouse.
Two things go wrong when I use Alt+Tab:
1. When I switch from invoice.xls to anything.xls, the deactivate event is
not triggered and my menus are not unloaded.
2. When I switch anything.xls to invoice.xls, the activate event is not
triggerd and my menus do not load.

The file anything.xls represents hundreds of files from individual users on
the network. I cannot place a macro in the open event of each of these to
check for a certain parameter before loading the menu. Even if this was
possible, it still wouldn't help me, because anything.xls would then be
loaded without a menu, and when I Alt+Tab back to invoice.xls, the activate
event is not fired and the menu's are not loaded.

If I misinterpreted your suggestion, could you please be a little more clear
about you mean...

Thanks and regards,
Gavin


Maybe you could base it on the path of the workbook if that's your rule.

if lcase(activeworkbook.path)....

You'd just add a check to each macro. If the user clicks on it, you could
beep
or issue a message that your macro isn't available.

(My first suggestion was to base that rule on the workbook name so you
wouldn't
need to add something to each workbook.)

 

[Gavin Frayne]

I gave it a shot. Nice approach, but still not quite what I need.

Gavin



Coincidentally, Stephen Bullen posted a response to a very similar question:
http://groups.google.com/[email protected]
(when I searched, google hadn't grabbed all the thread. Look for "Two AddIn
Questions" in the subject in your newsreader.)

But it does rely on putting something in the workbook that can be checked.

 

[Dave Peterson]

My suggestion was to let the menus appear, but block the execution if you're not
in a correct workbook.

Option Explicit
Sub testme()
If LCase(ActiveWorkbook.Path) <> "c:\templates\admin" Then
Beep
MsgBox "Cannot be used on this workbook"
Exit Sub
End If
'your real code here
End Sub

But since you're overwriting existing functions, you'd have to do more. You'd
actually have to execute the equivalent function in your code.

for your save replacement:

Option Explicit
Sub testme()
If LCase(ActiveWorkbook.Path) <> "c:\templates\admin" Then
ActiveWorkbook.Save
End If
'your original code here
End Sub

I think the suggestion is simple--but the implementation may be difficult.

Hi Dave,

I really appreciate your help, but I'm not sure if I understand how your
suggestion can help me. Let me try to be as clear as possible about what I
wish to achieve:

File 1: c:\templates\admin\invoice.xls
File 2: c:\user\anything.xls

When I open invoice.xls I want my menu's to appear. Among others, my menu's
overwrite the standard save, save as and paste commands.

Now, while I have invoice.xls open on my desktop, I want to be able to open
c:\user\anything.xls and the menu's must not appear.
This work perfectly when I use the mouse.
Two things go wrong when I use Alt+Tab:
1. When I switch from invoice.xls to anything.xls, the deactivate event is
not triggered and my menus are not unloaded.
2. When I switch anything.xls to invoice.xls, the activate event is not
triggerd and my menus do not load.

The file anything.xls represents hundreds of files from individual users on
the network. I cannot place a macro in the open event of each of these to
check for a certain parameter before loading the menu. Even if this was
possible, it still wouldn't help me, because anything.xls would then be
loaded without a menu, and when I Alt+Tab back to invoice.xls, the activate
event is not fired and the menu's are not loaded.

If I misinterpreted your suggestion, could you please be a little more clear
about you mean...

Thanks and regards,
Gavin

<<snipped>>

 

[Gavin Frayne]

Thanks Dave, I guess that would be the best way to get around. I'll give it
a shot.

Gavin



My suggestion was to let the menus appear, but block the execution if you're
not
in a correct workbook.

Option Explicit
Sub testme()
If LCase(ActiveWorkbook.Path) <> "c:\templates\admin" Then
Beep
MsgBox "Cannot be used on this workbook"
Exit Sub
End If
'your real code here
End Sub

But since you're overwriting existing functions, you'd have to do more.
You'd
actually have to execute the equivalent function in your code.

for your save replacement:

Option Explicit
Sub testme()
If LCase(ActiveWorkbook.Path) <> "c:\templates\admin" Then
ActiveWorkbook.Save
End If
'your original code here
End Sub

I think the suggestion is simple--but the implementation may be difficult.

Hi Dave,

I really appreciate your help, but I'm not sure if I understand how your
suggestion can help me. Let me try to be as clear as possible about what I
wish to achieve:

File 1: c:\templates\admin\invoice.xls
File 2: c:\user\anything.xls

When I open invoice.xls I want my menu's to appear. Among others, my menu's
overwrite the standard save, save as and paste commands.

Now, while I have invoice.xls open on my desktop, I want to be able to open
c:\user\anything.xls and the menu's must not appear.
This work perfectly when I use the mouse.
Two things go wrong when I use Alt+Tab:
1. When I switch from invoice.xls to anything.xls, the deactivate event is
not triggered and my menus are not unloaded.
2. When I switch anything.xls to invoice.xls, the activate event is not
triggerd and my menus do not load.

The file anything.xls represents hundreds of files from individual users on
the network. I cannot place a macro in the open event of each of these to
check for a certain parameter before loading the menu. Even if this was
possible, it still wouldn't help me, because anything.xls would then be
loaded without a menu, and when I Alt+Tab back to invoice.xls, the activate
event is not fired and the menu's are not loaded.

If I misinterpreted your suggestion, could you please be a little more clear
about you mean...

Thanks and regards,
Gavin

<<snipped>>