Disable Right Clicking of Excel logo on Standard menu Bar

P

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
 
R

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
 
P

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
 
P

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
 
P

Peter Rooney

Chijanzen,

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

Thank you

Pete
 
C

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
 
R

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
 
P

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
 
P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top