Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
US / EN VBA menus
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
US / EN VBA menus
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
US / EN VBA menus
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
US / EN VBA menus
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" wrote in message ... 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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
US / EN VBA menus
"jojo" 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
US / EN VBA menus
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
US / EN VBA menus
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
US / EN VBA menus
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 "jojo" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sub-menus of main menus for Excel 2007 | New Users to Excel | |||
Creating Drop-down menus with subset drop-down menus | Excel Worksheet Functions | |||
difference between word 2003 menus and word 2007 menus-Explain pl | Excel Discussion (Misc queries) | |||
New Menus - attaching but menus are reset | Excel Worksheet Functions | |||
Overriding Actions of Excel DropDown Menus and Poup Menus | Excel Programming |