| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
protecting sheet tabs
Hi,
I've got a workbook where the sheet tabs are hidden but a user can simply un-hide them using the tools menu. Is it possible using VBA code to disable the tools menu when the workbook opens? Thanks in advance, Mike |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
protecting sheet tabs
why not make the sheets 'very' hidden:
Worksheets(1).Visible = xlVeryHidden Rgds J On Oct 23, 12:30 pm, Mike wrote: Hi, I've got a workbook where the sheet tabs are hidden but a user can simply un-hide them using the tools menu. Is it possible using VBA code to disable the tools menu when the workbook opens? Thanks in advance, Mike |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
protecting sheet tabs
I don't think this helps because I'm not trying to hide the sheet. What I'm
trying to do is hide the sheet tab only and stop users un-hiding the tabs |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
protecting sheet tabs
to the best of my knowledge, this is not something you can prevent.
Removing the options menu seems a little drastic, but Application.Commandbars("Tools").Controls("&Option s...").Visible = False -- Regards, Tom Ogilvy "Mike" wrote: I don't think this helps because I'm not trying to hide the sheet. What I'm trying to do is hide the sheet tab only and stop users un-hiding the tabs |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
protecting sheet tabs
Tom,
That does exactly what I want. Thank you. Mike "Tom Ogilvy" wrote: to the best of my knowledge, this is not something you can prevent. Removing the options menu seems a little drastic, but Application.Commandbars("Tools").Controls("&Option s...").Visible = False -- Regards, Tom Ogilvy "Mike" wrote: I don't think this helps because I'm not trying to hide the sheet. What I'm trying to do is hide the sheet tab only and stop users un-hiding the tabs |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
protecting sheet tabs
Should this work in 2003 if so where should it be placed, thanks in advanvce.
"Tom Ogilvy" wrote: to the best of my knowledge, this is not something you can prevent. Removing the options menu seems a little drastic, but Application.Commandbars("Tools").Controls("&Option s...").Visible = False -- Regards, Tom Ogilvy "Mike" wrote: I don't think this helps because I'm not trying to hide the sheet. What I'm trying to do is hide the sheet tab only and stop users un-hiding the tabs |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
protecting sheet tabs
You could have two macros placed in a General module for the workbook that you
want to lock down. Option Explicit Sub Auto_Open() Application.Commandbars("Tools").Controls("&Option s...").Visible = False End Sub Sub Auto_Close() Application.Commandbars("Tools").Controls("&Option s...").Visible = True End Sub The Auto_Open routine will run each time the workbook is opened (if the user allows macros to run). And it'll hide the options choice. The Auto_Close routine will run when the workbook is closed. Benijofar Dave wrote: Should this work in 2003 if so where should it be placed, thanks in advanvce. "Tom Ogilvy" wrote: to the best of my knowledge, this is not something you can prevent. Removing the options menu seems a little drastic, but Application.Commandbars("Tools").Controls("&Option s...").Visible = False -- Regards, Tom Ogilvy "Mike" wrote: I don't think this helps because I'm not trying to hide the sheet. What I'm trying to do is hide the sheet tab only and stop users un-hiding the tabs -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
protecting sheet tabs
Hi Dave,
I have tried using your two Macros to hide the Tabs in my workbook. I have placed the two macros in a Module (module 12), but nothing seems to happen. I save and close the workbook, but when I re-open it, the tabs are still there. Have I placed the macros in the wrong area? Regard Ant "Dave Peterson" wrote: You could have two macros placed in a General module for the workbook that you want to lock down. Option Explicit Sub Auto_Open() Application.Commandbars("Tools").Controls("&Option s...").Visible = False End Sub Sub Auto_Close() Application.Commandbars("Tools").Controls("&Option s...").Visible = True End Sub The Auto_Open routine will run each time the workbook is opened (if the user allows macros to run). And it'll hide the options choice. The Auto_Close routine will run when the workbook is closed. Benijofar Dave wrote: Should this work in 2003 if so where should it be placed, thanks in advanvce. "Tom Ogilvy" wrote: to the best of my knowledge, this is not something you can prevent. Removing the options menu seems a little drastic, but Application.Commandbars("Tools").Controls("&Option s...").Visible = False -- Regards, Tom Ogilvy "Mike" wrote: I don't think this helps because I'm not trying to hide the sheet. What I'm trying to do is hide the sheet tab only and stop users un-hiding the tabs -- Dave Peterson . |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
protecting sheet tabs
Did you allow macros to run?
If you run the Auto_Open procedure yourself--just open the VBE and select that procedure and F5, does it work? Ant wrote: Hi Dave, I have tried using your two Macros to hide the Tabs in my workbook. I have placed the two macros in a Module (module 12), but nothing seems to happen. I save and close the workbook, but when I re-open it, the tabs are still there. Have I placed the macros in the wrong area? Regard Ant "Dave Peterson" wrote: You could have two macros placed in a General module for the workbook that you want to lock down. Option Explicit Sub Auto_Open() Application.Commandbars("Tools").Controls("&Option s...").Visible = False End Sub Sub Auto_Close() Application.Commandbars("Tools").Controls("&Option s...").Visible = True End Sub The Auto_Open routine will run each time the workbook is opened (if the user allows macros to run). And it'll hide the options choice. The Auto_Close routine will run when the workbook is closed. Benijofar Dave wrote: Should this work in 2003 if so where should it be placed, thanks in advanvce. "Tom Ogilvy" wrote: to the best of my knowledge, this is not something you can prevent. Removing the options menu seems a little drastic, but Application.Commandbars("Tools").Controls("&Option s...").Visible = False -- Regards, Tom Ogilvy "Mike" wrote: I don't think this helps because I'm not trying to hide the sheet. What I'm trying to do is hide the sheet tab only and stop users un-hiding the tabs -- Dave Peterson . -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
protecting sheet tabs
Good Morning Dave,
I am using Excel 2007 and the file type is .xlsm. I have manually run the macro, both by choosing the Macro out of the Macro list, and also by running it from within the VB code screen. I have also saved the program and then closed and opened it a number of times, but nothing seems to happen. I copied and pasted your code as follows which is located in Module 12 Option Explicit Sub Auto_Open() Application.CommandBars("Tools").Controls("&Option s...").Visible = False End Sub Sub Auto_Close() Application.CommandBars("Tools").Controls("&Option s...").Visible = True End Sub Co-incidentally I have also tried using another Macro to suppress the "Save" dialogue box when exiting Excel, but it did not seem to work either. Thank you Ant "Dave Peterson" wrote: Did you allow macros to run? If you run the Auto_Open procedure yourself--just open the VBE and select that procedure and F5, does it work? Ant wrote: Hi Dave, I have tried using your two Macros to hide the Tabs in my workbook. I have placed the two macros in a Module (module 12), but nothing seems to happen. I save and close the workbook, but when I re-open it, the tabs are still there. Have I placed the macros in the wrong area? Regard Ant "Dave Peterson" wrote: You could have two macros placed in a General module for the workbook that you want to lock down. Option Explicit Sub Auto_Open() Application.Commandbars("Tools").Controls("&Option s...").Visible = False End Sub Sub Auto_Close() Application.Commandbars("Tools").Controls("&Option s...").Visible = True End Sub The Auto_Open routine will run each time the workbook is opened (if the user allows macros to run). And it'll hide the options choice. The Auto_Close routine will run when the workbook is closed. Benijofar Dave wrote: Should this work in 2003 if so where should it be placed, thanks in advanvce. "Tom Ogilvy" wrote: to the best of my knowledge, this is not something you can prevent. Removing the options menu seems a little drastic, but Application.Commandbars("Tools").Controls("&Option s...").Visible = False -- Regards, Tom Ogilvy "Mike" wrote: I don't think this helps because I'm not trying to hide the sheet. What I'm trying to do is hide the sheet tab only and stop users un-hiding the tabs -- Dave Peterson . -- Dave Peterson . |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
protecting sheet tabs
Sorry Dave but I should have been clearer in my last post.
I am trying to hide the worksheet Tabs so they are not visible to the user. Not trying to hide the worksheets, just trying to hide the tabs I have found a workaround. If I hide the ribbon and the formula bar, then there is no way the user can get into the Excel options to turn on Tabs again. This an even better option because my workspace has increased and the user now has no option but to use only the buttons provided. Thanks Anthony "Dave Peterson" wrote: Did you allow macros to run? If you run the Auto_Open procedure yourself--just open the VBE and select that procedure and F5, does it work? Ant wrote: Hi Dave, I have tried using your two Macros to hide the Tabs in my workbook. I have placed the two macros in a Module (module 12), but nothing seems to happen. I save and close the workbook, but when I re-open it, the tabs are still there. Have I placed the macros in the wrong area? Regard Ant "Dave Peterson" wrote: You could have two macros placed in a General module for the workbook that you want to lock down. Option Explicit Sub Auto_Open() Application.Commandbars("Tools").Controls("&Option s...").Visible = False End Sub Sub Auto_Close() Application.Commandbars("Tools").Controls("&Option s...").Visible = True End Sub The Auto_Open routine will run each time the workbook is opened (if the user allows macros to run). And it'll hide the options choice. The Auto_Close routine will run when the workbook is closed. Benijofar Dave wrote: Should this work in 2003 if so where should it be placed, thanks in advanvce. "Tom Ogilvy" wrote: to the best of my knowledge, this is not something you can prevent. Removing the options menu seems a little drastic, but Application.Commandbars("Tools").Controls("&Option s...").Visible = False -- Regards, Tom Ogilvy "Mike" wrote: I don't think this helps because I'm not trying to hide the sheet. What I'm trying to do is hide the sheet tab only and stop users un-hiding the tabs -- Dave Peterson . -- Dave Peterson . |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
protecting sheet tabs
I read your other message and it looks like you've found a solution.
But the code that I posted works on versions of excel before the ribbon. (It may stop using the alt-keys to get to that Options menu), but the ribbon is a completely different beast.) If you want to learn about modifying the ribbon, you can start at Ron de Bruin's site: http://www.rondebruin.nl/ribbon.htm http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an addin) or http://www.rondebruin.nl/2007addin.htm Ant wrote: Good Morning Dave, I am using Excel 2007 and the file type is .xlsm. I have manually run the macro, both by choosing the Macro out of the Macro list, and also by running it from within the VB code screen. I have also saved the program and then closed and opened it a number of times, but nothing seems to happen. I copied and pasted your code as follows which is located in Module 12 Option Explicit Sub Auto_Open() Application.CommandBars("Tools").Controls("&Option s...").Visible = False End Sub Sub Auto_Close() Application.CommandBars("Tools").Controls("&Option s...").Visible = True End Sub Co-incidentally I have also tried using another Macro to suppress the "Save" dialogue box when exiting Excel, but it did not seem to work either. Thank you Ant "Dave Peterson" wrote: Did you allow macros to run? If you run the Auto_Open procedure yourself--just open the VBE and select that procedure and F5, does it work? Ant wrote: Hi Dave, I have tried using your two Macros to hide the Tabs in my workbook. I have placed the two macros in a Module (module 12), but nothing seems to happen. I save and close the workbook, but when I re-open it, the tabs are still there. Have I placed the macros in the wrong area? Regard Ant "Dave Peterson" wrote: You could have two macros placed in a General module for the workbook that you want to lock down. Option Explicit Sub Auto_Open() Application.Commandbars("Tools").Controls("&Option s...").Visible = False End Sub Sub Auto_Close() Application.Commandbars("Tools").Controls("&Option s...").Visible = True End Sub The Auto_Open routine will run each time the workbook is opened (if the user allows macros to run). And it'll hide the options choice. The Auto_Close routine will run when the workbook is closed. Benijofar Dave wrote: Should this work in 2003 if so where should it be placed, thanks in advanvce. "Tom Ogilvy" wrote: to the best of my knowledge, this is not something you can prevent. Removing the options menu seems a little drastic, but Application.Commandbars("Tools").Controls("&Option s...").Visible = False -- Regards, Tom Ogilvy "Mike" wrote: I don't think this helps because I'm not trying to hide the sheet. What I'm trying to do is hide the sheet tab only and stop users un-hiding the tabs -- Dave Peterson . -- Dave Peterson . -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| protecting formulas without protecting sheet so grouping still wor | Excel Discussion (Misc queries) | |||
| tabs are missing even though 'tools-options-view-sheet tabs' ok? | Excel Worksheet Functions | |||
| Protecting Worksheet Tabs | Excel Programming | |||
| frustrated - protecting and tabs | Excel Discussion (Misc queries) | |||
| Protecting Excel Options-Tabs | Excel Programming | |||
Linear Mode
