How to run sub menu items from VBA

[Mangesh]

I am trying to run a sub menu item from the toolbar through VBA. How
do i do it? Lets say for example, i want to run Data > Sort.

CommandBars("Worksheet Menu Bar").Controls("Data").Index gives me the
index of menu item 'Data'. But how to get the index for Sort.

something like this is what i am looking for, although this is not
valid
CommandBars("Worksheet Menu Bar").Controls("Data").Controls("Sort")

Basically i want to automate one of the submenu actions.

Mangesh

 

[Mike H]

Hi,

Unless you have a compelling reason for doing it that way it's far simpler
to perform a Vb sirt like this

Range("B1:B100").Sort Key1:=Range("B1"), Order1:=xlAscending

Mike

 

[Bob Phillips]

Why not just run a VBA sort?

 

[Jim Rech]

While you can execute commandbar items, using the Execute method, it's
better to use the Range objects Sort method directly. It gives you a lot
more control.
--
Jim
|I am trying to run a sub menu item from the toolbar through VBA. How
| do i do it? Lets say for example, i want to run Data > Sort.
|
| CommandBars("Worksheet Menu Bar").Controls("Data").Index gives me the
| index of menu item 'Data'. But how to get the index for Sort.
|
| something like this is what i am looking for, although this is not
| valid
| CommandBars("Worksheet Menu Bar").Controls("Data").Controls("Sort")
|
| Basically i want to automate one of the submenu actions.
|
| Mangesh

 

[Dave Peterson]

You don't need to go through the menus in your code to sort your data. You can
record a macro and you'll see the code.

On the other hand, if you wanted to display the dialog that users see when they
do a sort, you could use:

Application.Dialogs(xlDialogSort).Show

But if you know what needs to be sorted and how it should be sorted, then I
think it's better to just sort the data in code without any involvement of the
user.

 

[Mangesh]

Yes, thats my point. I want a way to run items from the comand bar.
Basically automate them. I know the other way of running the sort.
That was just an example. I want to run a sub-menu item from the main
menu. Basically automate the manual process of going to the main menu
and then the sub menu item and clicking it.

Thanks for your response Mike.

Mangesh

 

[Mangesh]

Basically there's a custom menu item with sub-items which I need to
automate. I don't have the macros attached to it though. The Data >
Sort was just an example which I mentioned.

Thanks for the response Bob.

Mangesh

 

[Mangesh]

Finally.. this is what I was looking for:

CommandBars("Data").Controls(1).Execute

or

CommandBars("Data").Controls("&Sort...").Execute

Thanks to all.

Mangesh