Thread: Re: US / EN VBA menus

Very interesting !!!

It doesn't work in french version because
Application.VBE.CommandBars("Menu Bar") is not kowned
Application.VBE.CommandBars("Barre de Menus") is knowned
but
Application.VBE.CommandBars(1) workes perfectly

I eventualy find the control I need :
In CommandBar 30007 (Tools) it was the 2578 control (VBAProject Properties)

But when in this dialogbox, impossible to manage without "SendKeys"
command. (As far as my knowledgement tells me)

And managiing from Access Application, the dialogBox doesn't open.

Nevertherless, the code you propose will be very usefull,

Many thanks fore all that,

Regards,

Joël


"Peter T" wrote:

> Sub VBE_MainMenu()
> Dim cb As CommandBar
> Dim ctr As CommandBarControl
> Set cb = Application.VBE.CommandBars("Menu Bar")
> For Each ctr In cb.Controls
> Debug.Print ctr.ID, ctr.Caption
> Next
> End Sub
>
> 30002 &File
> 30003 &Edit
> 30004 &View
> 30005 &Insert
> 30006 F&ormat
> 30165 &Debug
> 30012 &Run
> 30007 &Tools
> 30038 &Add-Ins
> 30009 &Window
> 30010 &Help
>
> Sub VBE_ViewMenu()
> Dim cbp As CommandBarPopup
> Dim ctr As CommandBarControl
> Set cbp = Application.VBE.CommandBars.FindControl(ID:=30004)
> For Each ctr In cbp.Controls
> Debug.Print ctr.ID, ctr.Caption
> Next
> End Sub
>
> 2558 &Code
> 2553 O&bject
> 939 &Definition
> 1822 Last Positio&n
> 473 &Object Browser
> 2554 &Immediate Window
> 2555 Local&s Window
> 2556 Watc&h Window
> 620 Call Stac&k...
> 2557 &Project Explorer ' << this one ?
> 222 Properties &Window
> 548 Toolbo&x
> 469 T&ab Order
> 30045 &Toolbars
> 106 Microsoft Excel
>
>
> Sub ShowPrjExp()
> ' for testing close the Project Explorer window
> Dim ctr As CommandBarControl
> Set ctr = Application.VBE.CommandBars.FindControl(ID:=2557)
> ctr.Execute
> End Sub
>
>
> > The underlined letter I wanted to know was the first "e" of "Poperties" in
> > tools menu
>
> I don't have Properties in the Tools menu
>
>
> If you are trying to programatically change Project properties, eg lock for
> viewing, add/remove password etc, I have something that can batch process
> files. It doesn't use SendKeys. (it's not a password cracker)
>
> Regards,
> Peter T
>
>
> "jojo" <jojo@discussions.microsoft.com> wrote in message
> news:F324E1F6-9ADE-4617-9311-2137FBC7EEF7@microsoft.com...
> > Thanks Peter,
> >
> > I suppose my english was not comprehensible.
> >
> > The menus I wanted to know about were those in VB Editor.
> >
> > I'm not sure we can use "execute" in order to protect the VBProject via a
> > macro.
> >
> > If you know a way to access the commandBAr of VB Editor, please tell us.
> >
> > The underlined letter I wanted to know was the first "e" of "Poperties" in
> > tools menu
> >
> > any way, lot of thanks for your answer.
> >
> > regards,
> >
> > Joël
> >
> > "Peter T" wrote:
> >
> >> Sub abc()
> >> Dim ctr As CommandBarControl
> >> For Each ctr In CommandBars("worksheet menu bar").Controls
> >> Debug.Print ctr.Caption
> >> Next
> >> End Sub
> >>
> >> &File
> >> &Edit
> >> &View
> >> &Insert
> >> F&ormat
> >> &Tools
> >> &Data
> >> A&ction
> >> &Window
> >> &Help
> >>
> >> Let me guess, you are thinking about SendKeys. Generally you can use
> >> myControl.Execute, use commandbars.findcontrol(ID:=x), no
> >> internationalization worries.
> >>
> >> > Properties of VBAProject (which letter is underline)
> >>
> >> Do you mean View, &Project Explorer, but you can use Ctrl-R in all
> >> versions
> >> (I guess?), but again better to use Excecute
> >>
> >> Regards,
> >> Peter T
> >>
> >>
> >>
> >> "jojo" <jojo@discussions.microsoft.com> wrote in message
> >> news:51F27DBB-C91F-47A4-A825-2427EA452667@microsoft.com...
> >> > Hello every body !
> >> >
> >> > I would like to know the exact english menu for VBA in excel
> >> >
> >> > File, View etc...
> >> >
> >> > But what I need above all, are the underlined letters for all these
> >> > menus,
> >> > specialy the tools menu (T, O, L or S underline), and in this menu,
> >> > what
> >> > about
> >> > Properties of VBAProject (which letter is underline)
> >> >
> >> > I'm sure you have understood that I have not the US version with me
> >> > ;-)))
> >> >
> >> > I need this in order to use in a protection vba project by macro, and
> >> > use
> >> > the "SendKeys" method. This might be operational either in French excel
> >> > version (that is mine, and the maco is OK) or US/UK version.
> >> >
> >> > Lot of thank's in advance,
> >> >
> >> > best regards,
> >> >
> >> > Joël
> >>
> >>
> >>
>
>
>

"jojo" <jojo@discussions.microsoft.com> wrote in message

In-line

> Very interesting !!!
>
> It doesn't work in french version because
> Application.VBE.CommandBars("Menu Bar") is not kowned
> Application.VBE.CommandBars("Barre de Menus") is knowned

Really, I am surprised. My understanding was English CommandBar names work
in all language versions. Perhaps the English name only works in Excel and
some other Office apps. Could you please test this -

Sub test()
Dim cb As CommandBar
Set cb = Application.CommandBars("Worksheet Menu Bar")
MsgBox cb.Name & vbCr & cb.NameLocal

End Sub


> I eventualy find the control I need :
> In CommandBar 30007 (Tools) it was the 2578 control (VBAProject
> Properties)

Ah, yes I follow what you are doing

> But when in this dialogbox, impossible to manage without "SendKeys"
> command. (As far as my knowledgement tells me)

There are various API methods to trigger or set all the controls that
dialog (and the other dialogs and msgbox's that might appear), but it's a
lot of work! If you are only processing one file SendKeys will probably
work OK (though not in Vista but there's a SendKeys workaround). However for
processing multiple files SendKeys is unlikely to be reliable.

Regards,
Peter T

typo, missing "in"

> There are various API methods to trigger or set all the controls IN that
> dialog (and the other dialogs and msgbox's that might appear)

Peter T

Hi ! Peter !

Back again to review some item...

The test you asked workes for Worksheets Application.CommandBars, but not
for VBE.CommandBars (Name and NameLocal are both French names)

But using VBE.CommandBars(1) ik OK

The following code works fine on 2000 Plateform (I suppose is OK on 2003 too)

Set ctr = .VBE.CommandBars.FindControl(ID:=2578)
ctr.Execute
.Wait (Now + TimeValue("0:00:5"))
.SendKeys "^{TAB}", True
.Wait (Now + TimeValue("0:00:3"))
.SendKeys "{+}", True
.SendKeys "{TAB}", True
.SendKeys "SGS2009", True
.Wait (Now + TimeValue("0:00:1"))
.SendKeys "{TAB}", True
.SendKeys "SGS2009", True
.ScreenUpdating = True
'Stop
.Wait (Now + TimeValue("0:00:10"))
.SendKeys "{ENTER}", True

On a vista / 2007 plateform ,it doesn't work properly.
If I delete the " ' " to execute the "Stop" Command, the dalogBox
VBEProject Properties is correctly displayed with all the infomations sent,
and typiing the enter key make the workbook correctly protected.

Without the stop command, the workbook is not protected... There is no error
message !!! the entire code is executed without problem, but no protection.

I hope my explainations are OK for you,

regards,

Joël

"Peter T" wrote:

> typo, missing "in"
>
> > There are various API methods to trigger or set all the controls IN that
> > dialog (and the other dialogs and msgbox's that might appear)
>
> Peter T
>
>
>