Hiding worksheets

  • Thread starter Thread starter Curtis
  • Start date Start date
C

Curtis

Is there any way to keep users from un-hiding worksheets in a workbook?
However they do require access to other sheets (not hiiden) within the
workbook

Thanks
 
Hi

Start VBA editor (Alt+F11). In VBA Project select sheet, and set it's
Visible property to xlSheetVeryHidden.

To prevent the user setting the sheets Visible property to xlSheetVisible
again, protect VBA Project with password.


Arvi Laanemets
 
Hi Curtis

Right click on the sheet tab>View Code>F4 to view the Properties
windows>scroll down to Visible>select xlSheetVeryHidden from the
dropdown on the right.

A Very Hidden sheet, does not show VBA and lock the project with a
password to prevent them opening the VB Editor.
 
Hi,

The short answer is you can't but you can make it more difficult. Alt+F11 to
open VB editor and if the 'Project' window and 'properties' window aren't
visible tap CTRL+R & F4 to show them

In the project window select the sheets in turn that you want to hide and in
the properties window set the 'visible' property to 'XlSheetVeryhidden'

Now click Tools|VBA Project properties and on the 'protection' tab check
'Lock project for viewing' and apply a password. Save close and re-open.

It's now as difficult as it gets and the only way to make the sheet visible
is from the VBA editor but it isn't secure because that's the nature of Excel
security
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
Thanks

I have another question if I may. I have 30 sheets in a particular workbook
and each is password protected with certain cells within each sheet unlocked
for user inputting. Is ther a way to lock/ unlock multiple sheets at a time
(same password)

Thanks
ce
 
After hiding the sheets, protect the workbook under Tools>Protection.

This will prevent unhiding sheets.


Gord Dibben MS Excel MVP
 
Sub ProtectAllSheets()
Application.ScreenUpdating = False
Dim N As Single
For N = 1 To Sheets.Count
Sheets(N).Protect Password:="justme"
Next N
Application.ScreenUpdating = True
End Sub

Sub UnprotectAllSheets()
Application.ScreenUpdating = False
Dim N As Single
For N = 1 To Sheets.Count
Sheets(N).Unprotect Password:="justme"
Next N
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP
 
Hi,

Is ther a way to lock/ unlock multiple sheets at a time (same password)

Once again No. But you can loop through them and do it. Alt+f11 to open
vbeditor. Right click 'ThisWorkbook' and insert module and paste the code
below in.

Change this line
S = "Sheet1,Sheet2,Sheet3"

to contain the names off all the sheet to unlock. Run the code and your done


Sub Sonic()
Dim ws As Worksheet
Dim V As Variant
Dim S As String
S = "Sheet1,Sheet2,Sheet3"
V = Split(S, ",")
For Each ws In ThisWorkbook.Worksheets
If Not IsError(Application.Match(ws.Name, V, 0)) Then
ws.Unprotect Password:="MyPass"
End If
Next
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
Back
Top