Disable Unhide Control

[Vicky9063]

Hi,
How do i disable the Format/Sheet/Unhide control function in excel so that the user does not interfere with my macros??
Cheers
V

 

[Damon Ostrander]

Hi Vicky,

This is a bit long, but here goes...

Application.CommandBars("Worksheet Menu Bar").Controls("F&ormat").Controls("S&heet").Controls("&Unhide...").Enabled = False

Whew!

Keep Excelling.

Damon

 

[Vicky9063]

Hi Damon,
Thanks for that!
Cheers
Vicky

 

[mvita]

Hi Vicky,

This is a bit long, but here goes...

Application.CommandBars("Worksheet Menu Bar").Controls("F&ormat").Controls("S&heet").Controls("&Unhide...").Enabled = False

Whew!

Keep Excelling.

Damon

Hi,
This works Great.
How about the Unhide menu available through the mouse rightclick?
Is there a similar "simple code" for this also?
Thanks a lot and kind regards.

BTW, is this code ONLY for the sheet or Global (pertaining to all sheets?)

 

[Barry Katcher]

If, after hiding a worksheet, you use Tools>Protection>Protect Workbook, the hidden worksheets cannot be unhidden via Format>Sheet>Unhide - you first have to Unprotect Workbook in order to show them. If you add a password to Protect Workbooks, this makes it even more secure.

If you have macros that need to show or use the hidden worksheets, you can incorporate an Unprotect Workbook line at the beginning of the macro and Protect Workbook line at the end.

 

[mvita]

Barry,
Lighting fast repy. Thanks.
Guess your option to use the Protection is not the "issue". What I wanted is to Disable (Gray out) the Hide and Unhide menu's from
(1) Row
(2) Column
(3) Sheet
Thus, the user can see that these menu's exist but can not be selected.
Mind you, the code suggested above is for ALL GLOBAL excel, and thus I will need to unload=reset the main menus ([Application.CommandBars) on both the Main menu and also from the mouse right click menu before closing / existing (with or without saving) the file.

Any help on how to go about this? If possible, please supply code......

Kind regards.
Akil

 

[Barry Katcher]

Firstly, using Tools>Protection>Protect Workbook is NOT global - it only applies to that workbook. This will gray out the hide and unhide options. Here's two macros, one to protect with a password and the other to unprotect:

Sub ProtectWB()
    ActiveWorkbook.Protect Password:="your_password", Structure:=True, Windows:=False
End Sub

Sub UnprotectWB()
    ActiveWorkbook.Unprotect Password:="your_password"
End Sub

Secondly, you can use Tools>Protection>Protect Sheet to gray out the row and the column hide and unhide options. Again, this is not global, it's not even workbook wide, but applies only to the worksheet. Here's code:

Sub ProtectSheet()
    ActiveSheet.Protect Password:="your_password", DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Sub UnprotectSheet()
    ActiveSheet.Unprotect Password:="your_password"
End Sub

As far as I can understand, you don't seem to need any "On Closing" macro. Just insert whatever your code is in between the two sets of macros above.

 

[mvita]

Barry,
Thanks for the password msg. I am looking for a solutions without the password protect as an option. I have this so far:

'Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
' Dim cb As CommandBar, cbControl As CommandBarControl, cbButton As CommandBarButton
' Set cb = Application.CommandBars("Column")
' For Each cbControl In cb.Controls
' If cbControl.Caption = "&Unhide" Then
' cbControl.Delete
' End If
' Next
'End Sub

I can use this for the &Row &Sheet also, and expand to &Hide

Where I am getting stuck is to "unload this code" when exiting the file! With the above, all new/existing excel wb's are without the Unhide meu. I can not seem to get it back by giving the

Application.CommandBars("Column").Reset

code.

Maybe I am not following the correct procedure.

Hope you can help out?

Akil

 

[Barry Katcher]

Sorry, Akil - my macro knowledge is pretty much limited to recorded macros. Hopefully, someone else on the site can help you with this.

 

[mvita]

Thanks Barry,

Lets hope some one picks this up........

Kind regards.

Akil