Is it possible to lock all access to a particular sheet in a workbook?

[Woblypegs]

Hi folks, just wanting to know if it is possible to password lock a certain sheet in a book? By this I mean no-one can access the sheet without the password. I know you can password protect sheets where data can be protected but I need access to the sheet to be blocked unless you have the password.
We have a workbook that is shared and we want to add another sheet that uses the data from the existing sheets as well as has new data that we don't want the people this workbook is shared with to have access to.

Many Thanks
Wobly

 

[Michael M]

Maybe this

Sub MM1()
Dim ws As Worksheet, ans As String
ans = InputBox("Enter your password")
  Select Case ans
    Case "password" 'change password to suit
        Sheets("Sheet1").Visible = xlVeryHidden
    Case "all" 'change password to suit
        For Each ws In Worksheets
                ws.Visible = True
        Next
    Case Else
        err = MsgBox("Incorrect Password", vbExclamation)
    End Select
End Sub

 

[Woblypegs]

Awesome Thanks very much. will have a play and see how it goes.
Wobly

 

[Michael M]

glad to help...?

 

[Woblypegs]

Hi again, I am fairly new to this excel stuff and have never used the VBA in excel. You may have to talk me through how I activate this code, I changed sheet1 to the sheet I wanted it to control (sheet3 in this case) and then entered the code in the vba editor first under the workbook and then under the sheet I want it to control to see which would work but nothing happened in either. What am I doing wrong?

Many Thanks
Wobly

 

[Michael M]

Ok, it can go in the "This Workbook" module.
Then to run it you go back to the workbook and either press ALT + F8 and then press run
OR
You can put a shape on the worksheet and r click the shape and select assign macro....then you just have to press the shape to activate the macro.....don't put the shape on sheet 3, of course

 

[Woblypegs]

Sorry for being a bit thick on this,but I've got the code working however, how do I set the password in the first place? it asks for the password but then just says incorrect password or subscript out of range.

Thanks
Wobly

 

[Michael M]

You don't need to password protect the sheet, simply put a password in this line

ans = InputBox("Enter your password")

and then change the "password" in this line to the one you have inputted into the line above

Case "password" 'change password to suit

 

[Woblypegs]

Hi Michael M, I did try that, Anyway this is what the code looks like.

but when I run it all I get is a message saying "Subscript out of range".
Wobly

 

[Michael M]

In that case you need to check your sheet name.
Check for extra spaces at each end...also make sure the "Sheet3" isn't "sheet 3" or "SHEET3"