disable unhide sheets on toolbar

[ditchy]

Hello
is there a way to dis-able the unhide sheets toolbar?
TIA, Ditchy

 

[Harald Staff]

Hi Ditchy

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

HTH. Best wishes Harald

 

[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)

 

[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

 

[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

 

[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

 

[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

 

[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

 

[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)