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

Gavin



"Dave Peterson" wrote in message
...
Coincidentally, Stephen Bullen posted a response to a very similar question:
http://groups.google.com/groups?thre...40bmsltd.co.uk
(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 wrote:

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 wrote:

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

"Dave Peterson" wrote in message
...
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 wrote:

Thanks for the confirmation Tom. Do you know of any workaround? If the
people using my excel worksheet always use alt+tab to switch between
workbooks, then they're always going to be stuck with my menus in the
wrong
workbook - which I would really like to avoid.

Any ideas anyone?

Gavin

"Tom Ogilvy" wrote:

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

Gavin Frayne wrote in message
...
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


--

Dave Peterson


--

Dave Peterson