Change Excel Menu

  • Thread starter Thread starter Jos Vens
  • Start date Start date
J

Jos Vens

Hi,

I wonder what's wrong in my procedure: I want to change the cut, copy and
paste menu in my custom menu procedures. I can make it work but I doubt if
it always (on every machine) will work.

I work with Excel 2000

This works:

Application.CommandBars(1).Controls(2).Controls(3).OnAction = "TB_Cut"
Application.CommandBars(1).Controls(2).Controls(4).OnAction = "TB_Copy"
Application.CommandBars(1).Controls(2).Controls(5).OnAction = "TB_Paste"

If the user changes the commandbar, I think the wrong menu-items will be
changed, so I changed my procedure without a result (my new procedure is not
applied to the menu)

Application.CommandBars.FindControl(ID:=21).OnAction = "TB_Cut"
Application.CommandBars.FindControl(ID:=19).OnAction = "TB_Copy"
Application.CommandBars.FindControl(ID:=22).OnAction = "TB_Paste"

I guess the second method is better, but does not the job.

Can anyone help?

Thanks
Jos Vens
 
Jos

It looks to me like the second set is finding a picture on
the command bars and setting the action command to the
command bar with that picture.

Another way to do this (although not very efficient) is to
loop through the command bars that are currently in the
application, and then when you find the one with the right
name and caption you can set the onaction of that node.

ie.

for x = 1 to application.commandbars.count
if application.commandbars(x).name = "THe name you want"
for y = 1 to application.commandbars(x).items
if application.commandbars(x).ITEM(y).caption = "The
caption" then
appli....onaction = "the action you want
exit for
next y
next x

so you can loop through also a dirty exit condition when
you find what you are looking for will save some execution
time.

But of cource I am sure there is a better way this might
just push you in the right direction. Also my code isn't
exactly right as I just guessed the names from what I
remember

Jase
 
Hi Jase,

two problems,

first: ID is the menu-id, what you think of is faceid which is the picture
second: localized versions of excel has other captions than not localized so
I can't loop like you do, I only can use the ID which is the unique
identifier of a menu(-item)

Thanks anyway
Jos
 
Back
Top