protecting sheet tabs

[Guest]

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

 

[WhytheQ]

why not make the sheets 'very' hidden:

Worksheets(1).Visible = xlVeryHidden

Rgds
J

 

[Guest]

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

 

[Guest]

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("&Options...").Visible = False

 

[Guest]

Tom,

That does exactly what I want.
Thank you.

Mike

 

[Benijofar Dave]

Should this work in 2003 if so where should it be placed, thanks in advanvce.

 

[Dave Peterson]

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("&Options...").Visible = False
End Sub
Sub Auto_Close()
Application.Commandbars("Tools").Controls("&Options...").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.

 

[Ant]

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]

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]

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("&Options...").Visible = False
End Sub
Sub Auto_Close()
Application.CommandBars("Tools").Controls("&Options...").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

 

[Ant]

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]

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