disable unhide sheets on toolbar

H

Harald Staff

Hi Ditchy

Protect the workbook (Tools > Protection menu) to prevent hiding, unhiding
and renaming of sheets.

HTH. Best wishes Harald
 
B

Bob Phillips

If you want to take out that control then use

Application.CommandBars("Worksheet Menu
Bar").Controls("Format").Controls("Sheet").Controls("Unhide...").Enabled =
True

but if you just want to stop a user unhiding one of your sheets, you can
make it very hidden, which means the user won't see it in the list of hidden
sheets a better way IMO)

Worksheets("mySheet").Visible = xlSheetVeryHidden

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
D

ditchy

Hi there Harald
when I (Tools > Protection menu)protect workbook, I can't run the my
unhide macro I keep getting error 400.
I need a way to keep the format sheet/unhide in-operable until a
password is entered for my macro to show sheets.
regards, Ditchy
 
D

ditchy

Hi BoB
I like this option
(((( Application.CommandBars("Works­heet Menu
Bar").Controls("Format").Contr­ols("Sheet").Controls("Unhide.­..").Enabled
=
True )))

but I need it to hide the format/sheets/toolbar on open,
then unhide the format/sheets/toolbar on close
not sure which way to go about it?
I have a macro that will unhide sheets when a password is entered, but
anyone could unhide if the format/sheets/unhide toolbar is visible.
regards, Ditchy
 
B

Bob Phillips

Hi Ditchy,

What Format|Sheets toolbar, I don't have one on my system? Is this a custom
button that you have added? Or do you mean the menu option (which is what I
referred to previously)?


--

HTH

RP
(remove nothere from the email address if mailing direct)


Hi BoB
I like this option
(((( Application.CommandBars("Works­heet Menu
Bar").Controls("Format").Contr­ols("Sheet").Controls("Unhide.­..").Enabled
=
True )))

but I need it to hide the format/sheets/toolbar on open,
then unhide the format/sheets/toolbar on close
not sure which way to go about it?
I have a macro that will unhide sheets when a password is entered, but
anyone could unhide if the format/sheets/unhide toolbar is visible.
regards, Ditchy
 
H

Harald Staff

Hi

Have your macro unprotect the workbook.

Sub Demo()
ThisWorkbook.Unprotect ("Harald")
ThisWorkbook.Sheets(1).Visible = _
Not ThisWorkbook.Sheets(1).Visible
ThisWorkbook.Protect ("Harald")
End Sub

You can of course hijack the menu instead, but then you should code it to be
disabled for this file only and leave Excel untouched for other files. Note
also that sheets can be hidden/shown also from the VB editor and by code if
the workbook is unprotected.

HTH. Best wishes Harald
 
D

ditchy

Hi BoB
The Worksheet Menu Bar
Format, cells,rows,columns,(sheets/hide,unhide)
toolbar on top of the screen
(File Edit View Insert Format Tools Data Window Help)
regards, Ditchy
 
B

Bob Phillips

That is the one that I refer to in the code I provided.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Top