US / EN VBA menus

[jojo]

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

 

[Peter T]

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]

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]

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]

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]

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

 

[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

 

[jojo]

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]

Hi Joël,

Thanks for testing the CommandBars Name property for language. OK, in Excel
it's English in all language versions but for the VBE it's the local
language, learnt something.


I quickly tried your code, it worked once or twice but not always, not sure
why. I don't think you need those Wait's, look into sending and Esc
beforehand. However Sendkeys is often unreliable.

On a vista / 2007 plateform ,it doesn't work properly.

I told you SendKeys doesn't work in Vista in my previous post (it does work
in a fully compiled VB6 app). There are various ways to simulate SendKeys
with API's, you could look into the SendInput and keybd_event Lib APIs. Keep
in mind you also need to send key-down and key-up events before/after the
keystroke with these APIs. I suggested following in another thread -

--------------------------------------------------------------------------------
Karl Peterson has provided an excellent replacement for SendKeys

http://vb.mvps.org/samples/project.asp?id=sendinput


Import the bas module MSendInput into your VBA project. This was written for
VB5/6 so some minor changes for VBA:


- add the following constant definitions at the top of the module
Const vbShiftMask = 1&
Const vbKeyScrollLock = 145&


- find and comment any lines starting Debug.Print


- remove
#If Not VB6 Then
Private Function Split etc


though if you need to cater for Excel97 you'll need to do something like
this
' Break into pieces, if possible.
#If VBA6 Then
pieces = Split(this, " ")
#Else
pieces = Split97(this, " ")
' Karl's VB5 function needs a little adaptation for Excel97
#End If
------------------------------------------------------------------------------------

Note this alternative also simulates key-strokes, you still have the same
problem of needing to ensure your window or dialog is active. As I mentioned
in an earlier post it is possible to control all the dialogs using API
methods, without SendKeys or equivalent. But it's a lot of work.

Regards,

Peter T