Password protect for a specific worksheet

[giggs11]

Hello:

My qestion is how do I send a workbook with 5-7 worksheets in it but set a password for viewing specific workseets(i.e Atlantic workshhet-only user from Atlantic can view this worksheet by entering an assigned password).

Is this possible?

Thanks!

 

[AnotherHiggins]

Just to make sure I understand what you're after -

You want to have users open the workbook and be greeted by a "log in" sheet - with all other sheets hidden - and then once they enter a name (or dept) and password they will see their own sheet and no others. Is that right?

If so, then yes it can be done.

Something similar (maybe even this particular question) has been covered here in the past.

A fairly common question is, "How can I force users to enable macros". The best answer is to set up your workbook so they can't use it unless they do enable macros. This involves setting all sheets but one to VeryHidden so on startup users can only see the one sheet. Once they enable macros, a workbook_open event unhides other sheet(s).

I found this walkthrough to save myself the time of writing it out here.

Once you have that in place, you just need to add some sort of login option.

[!]NOTE[/!]: Please bear in mind that, as with most workbook security, anyone who is determined and has access to the internet will be able to defeat your security and see the other hidden sheets. It would be fairly easy if they are familiar with VBA. What do I mean by "easy"? I actually have a button on a custom toolbar that unhides all sheets in the active workbook - even sheets that are "veryhidden".

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Hi,

Excel does not have strong password protection. But here's a process.

1. Make all the user-specific sheets' visible property xlsheetveryhidden. I'd even make ALL sheets except a SPLASH SHEET very hidden. Verry Hidden sheets can ONLY be made visible via VB or the VB Editor.

2. Give you VBA Project a password that only you and/or the administrator controls, so that no one else can get into the code.

3. When the user opens the workbook, the user must enable macros in order to be able to see anything but the splash sheet. The Workbook_Open event will contain the references for making sheets visible.

4. Use a UserForm Textbox, with the PasswordChar property assigned to capture the user's password.

5. Make the sheet(s) xlsheetvisible for that user.

6. The Worksheet_Close event will Save if necessary and place the workbook back into the initial state.



Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[giggs11]

anotherhiggins: Thanks but how do I set up a password for each worksheet?

 

[xlbo]

Part of the code that is executed asks for a password to view the sheet they have selected. Passwrod = correct. Sheet = visible. Password = incorrect. Sheet = not visible

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[giggs11]

Ok...it works now but how do I set up 1 password to open up more than 1 worksheet in the same workbook? It works for 1 worksheet.

 

[xlbo]

No - you don't use protection

You hold the passwords in the code

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[giggs11]

This is my code:

I want the user to be able to open up CalNov and CalDec with 1 password.

Option Explicit
Option Compare Text
'Password to unhide sheets
Const pWord = "Calgary"
Const pWord1 = "Quebec"
Const pWord2 = "Central"
Const pWord3 = "Western"

Sub HideSheets()
'Set worksheet to Very Hidden so that it can only be unhidden by a macro
Worksheets("CalNov").Visible = xlSheetVeryHidden
Worksheets("CalDec").Visible = xlSheetVeryHidden
Worksheets("Quebec").Visible = xlSheetVeryHidden
Worksheets("Central").Visible = xlSheetVeryHidden
Worksheets("Western").Visible = xlSheetVeryHidden
End Sub

Sub ShowSheets()
'Prompt the user for a password and unhide the worksheet if correct
Select Case InputBox("Please enter the password to unhide the sheet", _
"Enter Password")

Case pWord
With Worksheets("CalNov")
.Visible = xlSheetVisible
.Activate
.Range("A1").Select
End With


Case pWord1
With Worksheets("Quebec")
.Visible = xlSheetVisible
.Activate
.Range("A1").Select
End With

Case pWord2
With Worksheets("Central")
.Visible = xlSheetVisible
.Activate
.Range("A1").Select
End With

Case pWord3
With Worksheets("Western")
.Visible = xlSheetVisible
.Activate
.Range("A1").Select
End With
Case Else
MsgBox "Sorry, that password is incorrect!", _
vbCritical + vbOKOnly, "You are not authorized!"

End Select
End With

End Sub

 

[xlbo]

Really? Can you not logically deduce:

Case pWord
With Worksheets("CalNov")
.Visible = xlSheetVisible
.Activate
.Range("A1").Select
End With
With Worksheets("CalDec")
.Visible = xlSheetVisible
.Activate
.Range("A1").Select
End With




Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[AnotherHiggins]

This isn't your question - but one thing I see right off that bat is that you set sheets to VeryHidden at the beginning of your code. If someone just presses "NO" when asked if they want to enable macros, that means they'll see all sheets.

You need to do the hiding in an BeforeClose event so that the default state of the workbook the next time it is opened is to have all sheets hidden.

As for your question - Aren't there set groups of people that should see set groups of worksheets? Assuming there are, just set up one password for each group. A single password can unhide as many sheets as you want - even all of them.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[giggs11]

Geoff,

Funny...I did try that and it didn't work but I tried it again it's ok now.

Thanks,
Simon