CAN YOU HIDE A WORKSHEET MENU BAR

W

WayneJ

New Member
Joined
Oct 9, 2002
Messages
27
Can you hide a worksheet menu bar ?

I have a PROTECTED Worksheet for employees where they click on a macro button to save the worksheet in read only so it can't be accidently deleted, and it is also saved in a backup folder.

But I dont want employees to be able to click on Save in the File Menu, only the Save Macro button I have created.

Is this possible

Wayne :)
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
Hi Wayne,

What about Ctrl&S ?

Rather than disable various save options, how about intercepting the save event itself?

Try this instead (the BeforeSave event code needs to go in the ThisWorkboook object).
Code: 
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If bAllowSave = False Then
    Cancel = True
    MsgBox "Please only use the custom save button!"
End If

End Sub

Public bAllowSave As Boolean

Sub SaveTest()
bAllowSave = True
'allow saves
ThisWorkbook.Save
'your save routine
bAllowSave = False
'prevent saves
End Sub
HTH
 
Upvote 0
Thanks for the reply Richie ! :)

I do beleive this will work but it is not what I am looking for. Having a message box instructing staff to only use my custom "Save" button will only prompt the user to defy instructions and use the "Save" in File on the toolbar.

What I need is to be able to disable the COMPLETE Toolbar so NO OTHER OPTION IS AVAILABLE.
Is this possible ?

Wayne :)
 
Upvote 0
you can put code like this in the ThisWorkbook module:


Private Sub Workbook_Activate()
Application.CommandBars("Standard").Enabled = False
Application.CommandBars("Worksheet Menu Bar").Controls("File").Controls("Save").Enabled = False
Application.OnKey "^s", "" 'Disable Ctrl + S shortcut
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Standard").Enabled = True
Application.CommandBars("Worksheet Menu Bar").Controls("File").Controls("Save").Enabled = True
Application.OnKey "^s" 'Enable Ctrl + S shortcut
End Sub

Private Sub Workbook_Deactivate()
Application.CommandBars("Standard").Enabled = True
Application.CommandBars("Worksheet Menu Bar").Controls("File").Controls("Save").Enabled = True
Application.OnKey "^s" 'Enable Ctrl + S shortcut
End Sub

Private Sub Workbook_Open()
Application.CommandBars("Standard").Enabled = False
Application.CommandBars("Worksheet Menu Bar").Controls("File").Controls("Save").Enabled = False
Application.OnKey "^s", "" 'Disable Ctrl + S shortcut

End Sub
 
Upvote 0
But I should mention that using this could cause more problems than it solves (for example if Dr. Watson shuts down Excel while the user is using your workbook, the user won't be able to get their Standard toolbar back without opening your file and closing it again.

It may be better to just put your special Save code in the Workbook_BeforeSave event in the ThisWorkBook module
 
Upvote 0
Thankyou for you replies and assistance Gentlemen!!

As always your knowledge is valued. This now gives me a couple of options to work on .

Wayne :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

X
  • Question
Remove filename only on title bar & not also on taskbar item?
Replies
0
Views
129
xunil76
X
sparky2205
  • Solved
Toggle CheckBox to Hide/Unhide worksheet
Replies
6
Views
392
sparky2205
sparky2205
W
  • Question
Protected sheet disables macro buttons
Replies
5
Views
327
warda11
W
K
  • Question
Can I automatically remove/prevent blank rows when saving as CSV from protected sheet with formulas?
Replies
1
Views
193
Warship
Warship
T
  • Solved
Copy worksheet to new workbook without formulas or macro button
Replies
9
Views
543
Tgrundy09
T

Forum statistics

Threads
1,214,829
Messages
6,121,826
Members
449,051
Latest member
excelquestion515

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top