Modify Menu behavior

[Lance]

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

 

[Lance]

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.

 

[Dave Peterson]

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

 

[Lance]

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?

 

[Dave Peterson]

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

 

[Lance]

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

 

[Lance]

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,

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