Modify Menu behavior

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lance - 22 Oct 2007 17:26 GMT

I am looking to modify the way the menus work in excel (specifically,
I am looking for menu changes on certain items).

I have created a custom menu bar, which allows users to access certain
files on our network, with specific search criteria.

In the custom menu, I have created a utility which allows the user to
change the search criteria before accessing the file. My one
complaint, is that everytime I select something in the menu (or any
menu), the menu closes after the action is performed. Is there anyway
to change the settings (either with VBA on a specific button
selection, or in general), to keep the menu open after clicking on an
item?

Thanks

Reply to this Message

Lance - 24 Oct 2007 17:12 GMT

I'm going to follow up with this question, and hope for the answer,
but also ask for an alternative if no one can figure out how to make
this work.

Ultimately, I would like my custom menu to be able to handle multiple-
user inputs at once, before closing itself. If a user wants to change
a couple of options at once, opening the menu 4-5 times is not really
viable. I have thought about making a custom form, but this seems a
bit more than I need..I am really just trying to enable/disable a few
options, and potentially add/remove items from a short list.

Ideas?

Thanks again.

> I am looking to modify the way the menus work in excel (specifically,
> I am looking for menu changes on certain items).
[quoted text clipped - 11 lines]
>
> Thanks

Reply to this Message

Dave Peterson - 24 Oct 2007 18:58 GMT

Maybe your menu system should show a userform that does all the work????

> I'm going to follow up with this question, and hope for the answer,
> but also ask for an alternative if no one can figure out how to make
[quoted text clipped - 25 lines]
> >
> > Thanks

Signature

Dave Peterson

Reply to this Message

Lance - 29 Oct 2007 17:34 GMT

I understand that is an option, but it would be much more time
intensive at this point to create a userform that handles all of this,
since my menu system already handles everything as I want - except for
staying open after certain items are selected.

Perhaps I am approaching this wrong. Is there a way to write a macro
to open a menu and make it visible? If I can do that, perhaps it would
be possible to have the menu close, and then just have it reopen to
where it was before the user clicked.

Does anyone have any idea whether this is possible?

> Maybe your menu system should show a userform that does all the work????
>
[quoted text clipped - 31 lines]
>
> Dave Peterson

Reply to this Message

Dave Peterson - 29 Oct 2007 19:02 GMT

Maybe you could assign an accellerator key to the option you want and then use
Sendkeys to send that key combination.

> I understand that is an option, but it would be much more time
> intensive at this point to create a userform that handles all of this,
[quoted text clipped - 43 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Lance - 29 Oct 2007 20:22 GMT

Dave,

This option did the trick - I had accelerator keys already, but I
didn't know about send keys....I will see if it works out as I hoped -
and if so, I will post my code for anyone elses future reference.

Thanks,

Lance

> Maybe you could assign an accellerator key to the option you want and then use
> Sendkeys to send that key combination.
[quoted text clipped - 50 lines]
>
> Dave Peterson

Reply to this Message

Lance - 29 Oct 2007 21:07 GMT

This worked great.

Here is a sample sub.

Sub reopenPrintOptionsMenu()
SendKeys "%npo"
End Sub

All I do is call this sub everytime I want to reopen that specific
menu. So every "print option" that a user changes, will then reopen
the menu. This action is duplicating if I were to hit Alt + n p o.

I then created a sub like this for each menu that I need to "reopen".
I create the sub instead of pasting the 1 line of code so that if I
change the hot keys in the future, I only need to update one place,
instead of everywhere I want to get at the menu from.

> Dave,
>
[quoted text clipped - 60 lines]
> >
> > Dave Peterson

Reply to this Message