Disable Right Clicking of Excel logo on Standard menu Bar

[Peter Rooney]

Good afternoon, all,

I want to be able to prevent users from viewing my code. I've already
disabled Alt-F11 using application.onkey, but they can still get to it by
right clicking the Excel logo on the Standard Menu Bar. Is there any way in
which I can reference this as an object, to either disable it or remove it
from the menu bar?

Thanks in advance

Pete

 

[Ron de Bruin]

Hi Peter

It is easier to protect the VBA code with a password
In the VBA editor go to Tools>VBA project properties

 

[Peter Rooney]

Thanks, Ron, that worked just fine.

Are there any ways in which I can disable the other commands on the shortcut
menu to, for instance, prevent the creation of a new workbook?

Regards

Pete

 

[Ron de Bruin]

Hi Peter

See this page
http://www.rondebruin.com/menuid.htm

 

[Peter Rooney]

Ron,

This is extremely useful, as I have some knowledge of manipulating menus,
but I can't for the life of me see how to refer to the menu displayed when
you right click the Excel logo..!

Sorry to be dim, but do you know, please? I tried to delete all the commands
in the Worksheet menu bar with a for each, but the Excel logo's still there!
RRRRRRRR!

Thanks

Pete

 

[Ron de Bruin]

Hi Peter

Protect your workbook with Windows checked in Tools>Protection

 

[Peter Rooney]

Ron,

Just the job - have a green tick, with my thanks!

Pete

 

[Ron de Bruin]

You are welcome

Have a nice day

 

[chijanzen]

Peter:

try

Application.CommandBars("Document").Enabled = False

 

[Peter Rooney]

Chijanzen,

Thankfully it's hometime, but I'll give your suggestion a try first thing in
the morning.

Thank you

Pete

 

[Peter Rooney]

ChiJanZen,

I couldn't wait.

This is excellent!

Thank you VERY much!

Regards

Pete

 

[chijanzen]

Peter:

Other method，only Disabled viewing code CommandBarControl

Sub DisabledViewCode()
Dim Ctrl As CommandBarControl
For Each Ctrl In CommandBars.FindControls
If Ctrl.ID = 1561 Then
Ctrl.Enabled = False
End If
Next
End Sub

Sub EnabledViewCode()
Dim Ctrl As CommandBarControl
For Each Ctrl In CommandBars.FindControls
If Ctrl.ID = 1561 Then
Ctrl.Enabled = True
End If
Next
End Sub

 

[Ron de Bruin]

For the OP

Note: this is not working in Excel 97

You must use this then
(working in 97-2003)

Sub MenuControl_Enabled_False()
' Excel 97 - 2003
Dim Ctl As CommandBarControl
Dim Cbar As Integer

On Error Resume Next
For Cbar = 1 To Application.CommandBars.Count
For Each Ctl In Application.CommandBars(Cbar).Controls
Application.CommandBars(Cbar).FindControl(Id:=1561, _
Recursive:=True).Enabled = False
Next Ctl
Next Cbar
On Error GoTo 0
End Sub

Sub MenuControl_Enabled_True()
' Excel 97 - 2003
Dim Ctl As CommandBarControl
Dim Cbar As Integer

On Error Resume Next
For Cbar = 1 To Application.CommandBars.Count
For Each Ctl In Application.CommandBars(Cbar).Controls
Application.CommandBars(Cbar).FindControl(Id:=1561, _
Recursive:=True).Enabled = True
Next Ctl
Next Cbar
On Error GoTo 0
End Sub

 

[Peter Rooney]

Chijanzen,

My nine year old son "What did you at work today, dad?" was totally
intrigued by the fact that you'd signed your name in Chinese characters! I
had to explain to him that the rest of your message was in English, because
he had no idea how I'd understood your reply!

Once again, another useful solution. between you and Ron, I can now either
hide the Excel logo altogether, display but disable it, or disable specific
commands within its shortcut menu! Not bad for an afternoon's work!

Once again, thank you very much.

Pete

 

[chijanzen]

Peter :

I live in Taiwan

My English not too good

Please do not mind

chijanzen

 

[Peter Rooney]

Chijanzen,

It's not a problem - your English is superb!

I understood exactly what you meant and you solved the problem for me.

My son is interested in your part of the world (even if it's because he
loves Disney's "Mulan") and thought it was cool to have somebody writing to
me using (Mandarin/Cantonese?) characters!

Thanks again.

Hope all is well in Taiwan

Pete