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

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

"Mangesh" wrote:

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

Why not just run a VBA sort?

--
__________________________________
HTH

Bob

"Mangesh" wrote in message
...
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

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
"Mangesh" wrote in message
...
|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

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

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

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


On 21 Oct, 13:52, Mike H wrote:
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



"Mangesh" wrote:
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- Hide quoted text -

- Show quoted text -

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



On 21 Oct, 13:52, "Bob Phillips" wrote:
Why not just run a VBA sort?

--
__________________________________
HTH

Bob

"Mangesh" wrote in message

...



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- Hide quoted text -

- Show quoted text -

Finally.. this is what I was looking for:

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

or

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

Thanks to all.

Mangesh