VBE PROTECTION

[affordsol]

Hi to all !

I use the following two subs to enable/disable VBE for my users:
Sub DisableVBE()
Application.VBE.MainWindow.Visible = False '// Close ALL windows 1st!
CmdControl 1695, False '// Visual basics Editor
CmdControl 186, False '// Macros...
CmdControl 184, False '// Record New Macro...
CmdControl 1561, False '// View Code
CmdControl 1605, False '// Design Mode
Application.OnDoubleClick = "Dummy"
CommandBars("ToolBar List").Enabled = False
Application.OnKey "%{F11}", "Dummy"
'
CommandBars("Worksheet menu bar").Controls("Format") _
.Controls("Feuille").Enabled = False
CommandBars("Worksheet menu bar").Controls("Outils") _
.Controls("Protection").Enabled = False
End Sub

Sub EnableVBE()
CmdControl 1695, True '// Visual basics Editor
CmdControl 186, True '// Macros...
CmdControl 184, True '// Record New Macro...
CmdControl 1561, True '// View Code
CmdControl 1605, True '// Design Mode
Application.OnDoubleClick = ""
CommandBars("ToolBar List").Enabled = True
'Application.OnKey "%{F11}", ""
Application.OnKey "%{F11}"
'
CommandBars("Worksheet menu bar").Controls("Format") _
.Controls("Feuille").Enabled = True
CommandBars("Worksheet menu bar").Controls("Outils") _
.Controls("Protection").Enabled = True
End Sub


as you can see, these are "localized" for a french version of Excel through
the french words "Feuille" and "Outils"
I might of course write some new lines so as to handle english words
Nevertheless, I believe these quoted words could be replaced by quoted ids
Could anyone let me have these IDs ?

Thanks by advance & regards from Belgium,
Hervé

 

[Simon Murphy]

Hervé

try
?CommandBars("Worksheet menu bar").Controls("Format").Controls("sheet").id
in the immediate window
(gives 30026 here)
Also I have a feeeling, which could be wrong, that if you use the US English
names that will work in other languages. You can check that, I'm on an
English version so I can't. (please let me know if it does or not)
cheers
Simon

 

[affordsol]

Hi Simon, thanks for your post.

In fact, the english words would not work on a localized version.

Now, to solve the problem, I just investigated by doing :

Msgbox CommandBars("Worksheet menu bar").Controls("Format") _
.Controls("Feuille").index

Msgbox CommandBars("Worksheet menu bar").Controls("Outils").index

That gave me 4 and 6 for the answers, so, I just rewrote the two lines as
follows:

CommandBars("Worksheet menu bar").Controls("Format") _
.Controls(4).Enabled = True
CommandBars("Worksheet menu bar").Controls(6) _
.Controls("Protection").Enabled = True

And everything worked perfectly !


Regards from Belgium and thanks again for your cooperation,
Hervé+