Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default How to Disable Unhide Sheet menu option

How to disable unhide sheet menu option. i dont want people to unhide hidden
sheets in my excel workbook
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default How to Disable Unhide Sheet menu option

Try this:-

Private Sub Workbook_Open()
With Application.CommandBars("Format")
With .Controls("&Sheet")
.Controls("&Unhide...").Enabled = False
End With
End With
End Sub

Remebering of course to to put the same routing in the before_close event
with false changed to true.

Mike

"contactf" wrote:

How to disable unhide sheet menu option. i dont want people to unhide hidden
sheets in my excel workbook

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 638
Default How to Disable Unhide Sheet menu option

1-Open your Visual Basic Editor (Alt+F11).
2-Make sure the Project window is visible. If it isn't, go to View--
Project Explorer.

3-Make sure that the properties window is visible. If it isn't, goto
View--Properties Window.
4-In the Project Exolorer, find your workbook.
5-One at a time, select the sheets within that workbook that you don't
want the users to be able to unhide.
6-While the sheet is selected in the Project window, go down to the
Properties window and change the Visible property to
xlSheetVeryHidden.

This will cause any sheet set to xlSheetVeryHidden to not be displayed
in the Unhide Sheets dialog box. If no sheets are hidden other than
those set to xlSheetVeryHidden, then the Unhide Sheets menu option
will be grayed out.

contactf wrote:
How to disable unhide sheet menu option. i dont want people to unhide hidden
sheets in my excel workbook


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,123
Default How to Disable Unhide Sheet menu option

Hi

If your code must work also in non English versions use

Sub test()
Application.CommandBars("Worksheet Menu Bar").FindControl _
(ID:=891, Recursive:=True).Enabled = False
End Sub

More info here
http://www.rondebruin.nl/menuid.htm



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Mike H" wrote in message ...
Try this:-

Private Sub Workbook_Open()
With Application.CommandBars("Format")
With .Controls("&Sheet")
.Controls("&Unhide...").Enabled = False
End With
End With
End Sub

Remebering of course to to put the same routing in the before_close event
with false changed to true.

Mike

"contactf" wrote:

How to disable unhide sheet menu option. i dont want people to unhide hidden
sheets in my excel workbook

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,624
Default How to Disable Unhide Sheet menu option

And if there's a possibility that "Worksheet Menu Bar" isn't the active
menubar (do all non-English versions use that name?), you can even use:

Application.Commandbars.ActiveMenuBar.FindControl( _
ID:=891, Recursive:=True).Enabled = False


In article ,
"Ron de Bruin" wrote:

If your code must work also in non English versions use

Sub test()
Application.CommandBars("Worksheet Menu Bar").FindControl _
(ID:=891, Recursive:=True).Enabled = False
End Sub

More info here
http://www.rondebruin.nl/menuid.htm



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,123
Default How to Disable Unhide Sheet menu option

Hi J.E

You must use the English names for the command bars but the local names for the controls.
This is to make it easy <g

So the combination English command bar name with the ID of the control is always working

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"JE McGimpsey" wrote in message ...
And if there's a possibility that "Worksheet Menu Bar" isn't the active
menubar (do all non-English versions use that name?), you can even use:

Application.Commandbars.ActiveMenuBar.FindControl( _
ID:=891, Recursive:=True).Enabled = False


In article ,
"Ron de Bruin" wrote:

If your code must work also in non English versions use

Sub test()
Application.CommandBars("Worksheet Menu Bar").FindControl _
(ID:=891, Recursive:=True).Enabled = False
End Sub

More info here
http://www.rondebruin.nl/menuid.htm

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,624
Default How to Disable Unhide Sheet menu option

In article ,
"Ron de Bruin" wrote:

You must use the English names for the command bars but the local names for
the controls.
This is to make it easy <g


Ah.. Makes perfect MSense.

So the combination English command bar name with the ID of the control is
always working


Thanks. I never assume that the user hasn't replaced the Worksheet Menu
Bar with a custom menu bar (since my apps do that all the time).
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default How to Disable Unhide Sheet menu option

Hi,

Could you direct me to a list of these control Id's please.

Mike

"Ron de Bruin" wrote:

Hi

If your code must work also in non English versions use

Sub test()
Application.CommandBars("Worksheet Menu Bar").FindControl _
(ID:=891, Recursive:=True).Enabled = False
End Sub

More info here
http://www.rondebruin.nl/menuid.htm



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Mike H" wrote in message ...
Try this:-

Private Sub Workbook_Open()
With Application.CommandBars("Format")
With .Controls("&Sheet")
.Controls("&Unhide...").Enabled = False
End With
End With
End Sub

Remebering of course to to put the same routing in the before_close event
with false changed to true.

Mike

"contactf" wrote:

How to disable unhide sheet menu option. i dont want people to unhide hidden
sheets in my excel workbook


  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default How to Disable Unhide Sheet menu option

Apologies I must learn to read more carefully, you have provided the link
with this information. ;)

Mike

"Ron de Bruin" wrote:

Hi

If your code must work also in non English versions use

Sub test()
Application.CommandBars("Worksheet Menu Bar").FindControl _
(ID:=891, Recursive:=True).Enabled = False
End Sub

More info here
http://www.rondebruin.nl/menuid.htm



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Mike H" wrote in message ...
Try this:-

Private Sub Workbook_Open()
With Application.CommandBars("Format")
With .Controls("&Sheet")
.Controls("&Unhide...").Enabled = False
End With
End With
End Sub

Remebering of course to to put the same routing in the before_close event
with false changed to true.

Mike

"contactf" wrote:

How to disable unhide sheet menu option. i dont want people to unhide hidden
sheets in my excel workbook


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Unhide Main Menu Bar Dkline Excel Worksheet Functions 2 February 2nd 07 10:31 PM
Hide/Unhide Option - Need to consider all options! Turquoise_dax Excel Discussion (Misc queries) 3 June 15th 06 09:17 PM
disable customize option when right click on menu bar areddy Excel Discussion (Misc queries) 0 October 20th 05 09:14 AM
"Delete sheet" not an available option on File menu. cj6855 Excel Worksheet Functions 1 June 10th 05 09:22 PM
disable unhide sheets on toolbar ditchy Excel Discussion (Misc queries) 8 May 4th 05 10:10 AM


All times are GMT +1. The time now is 02:44 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"