PDA

View Full Version : how to hide all menus from excel woorkbook?



amit_nalawad
04-16-2009, 03:47 AM
Hi,

I want to hide all the menus from excel sheet after password proteection. Menus like File, Edit, View, etc.

How can i do this? Please help me.

Thanks

GTO
04-16-2009, 04:02 AM
In my ever so humble opinion, you'll want to think this one through. To be decently effective for more than the casual user... I (again, just my opinion) believe you'll want to look at all shortcut key combos, then all the commandbars, as well as replacing (at min) the menubar, lest the user cannot do anything...

Does that make any sense?

Mark

JONvdHeyden
04-16-2009, 04:11 AM
Also remember that hiding menu's will affect the entire application, not just that workbook. You will need to carefully code it such that the menu's are restored (and not reset) each time the workbook is closed or deactivated.

Remmember that users may have customised menus so that I why I mention that reset is not appropriate.

May I ask, why do you want to hide these?

GTO
04-16-2009, 04:21 AM
Remmember that users may have customised menus so that I why I mention that reset is not appropriate.


Hi Jon :-)

Wow(!), good point! I don't think I've ever 'violated' that , but I don't know that it would have occurred to me either...

Mark

Bob Phillips
04-16-2009, 04:21 AM
Option Explicit

Private mFormulaBar

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim oCB As CommandBar
For Each oCB In Application.CommandBars
oCB.Enabled = True
Next oCB

Application.DisplayFormulaBar = mFormulaBar
End Sub

Private Sub Workbook_Open()
Dim oCB As CommandBar
For Each oCB In Application.CommandBars
oCB.Enabled = False
Next oCB

mFormulaBar = Application.DisplayFormulaBar
Application.DisplayFormulaBar = False
End Sub


This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

GTO
04-16-2009, 04:32 AM
Hi Bob,

If a moment, wouldn't it be better to stick these in activate/deactivate?

Quite frankly, if I am missing something, your answer will be helpful, so figured better to ask than stare at the dang screen and wonder...

My thought is that this way if the user has 2(+) wb's open...

Thank you much,


Mark

amit_nalawad
04-16-2009, 05:05 AM
May I ask, why do you want to hide these?

I want this to give limited access to the user. User should not be able to save or save as the file or able to do any insert and edit operations.

JONvdHeyden
04-16-2009, 06:45 AM
Hello again

You can probably achieve these restrictions using a combination of worksheet protection and macro. You can lock cells for editing and apply worksheet protection to stop users editing.

And you can use a macro to prevent a user from saving the workbook:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
End Sub

Some sort of combination of this is safer than disabling the toolbars, in my humble opinion.