Setting Onaction for File > Save on Menu bar using item or index numbers

[David Cuthill]

I cannot seem to be able to set the onaction property of the File >
Save item on the worksheet menubar using index numbers.


Application.CommandBars("File").Controls("Save").OnAction = "SaveFile"

works fine but if I want to do it as follows I get an error message

Application.CommandBars(1).Controls(3).OnAction = "SaveFile"

I am trying to use this method since using names for the controlbar
and controls causes a problem if the workbook is used on a spanish
version of excel.

Also, I seem to be also have a problem with the use of decimals
between english and spanish. In the spanish version the decimal is a
comma and my code interpretes 1.234 as 1,234 or a number one thousand
times greater than it is supposed to be. Is there a work around for
this issue?


David

 

[Tom Ogilvy]

? commandbars(1).Name
Worksheet Menu Bar
? commandbars(1).Controls(3).Caption
&View


You need to use the ID to find the commandbar.

first find out the ID:

? commandbars("File").Controls("Save").Id
3

now you can use that to get a reference to the control:
set bar = commandbars.FindControl(ID:=3)
? bar.caption
&Save

Just be aware that some controls can appear on multiple commandbars, so you
may need to do additional searching and checking.

 

[David Cuthill]

Thank you for your help Tom - I was able to figure out
what to do. On a bigger related question what is the best
method for programming this type of language specific
stuff so that things work independant of the language that
is running on Excel?

Also do you have any thoughts on decimal "." versus ","
dilema?

 

[Tom Ogilvy]

VBA pretty much works in US English.

the only conflict I can see is if you are working with user entered text
strings.

so you can test and act accordingly:

? application.International(xlDecimalSeparator)
..
? application.International(xlThousandsSeparator)
,

 

[Tom Ogilvy]

Stephen Bullen has put Chapter 22 of his book Excel 2002 VBA Programmer's
Reference on line. This is the definitive chapter on international issues.

http://www.oaltd.co.uk/ExcelProgRef/Default.htm