Password Protection

[AllisonMartin]

Question for all the experts out there, I have an excel workbook that has several sheets to it.

One sheet has formulas, tables, and data that all other sheets look to at various points. This sheet will be hidden for every user on the server, but myself.

All other sheets will have certain ranges locked, that only within those ranges can they edit it at all.

My question is, is it possible to have a user enter their password upon opening the workbook, and only have their 1 relevant sheet to them, open up, and all others remain hidden?

As in: User1 would have their own sheet, and their own password. All other 8 sheets in the workbook would remain hidden, or not visible to them.

Any input is greatly appreciated!!

 

[AllisonMartin]

This is what I started with, but it's not fully working, so there is something I'm missing.

Private Sub Autpen()


On Error Resume Next


If Application.UserName = "JUSTINPC-PC" Then
Worksheets("Justin Steffens").Visible = True
Worksheets("Matt Johnson").Visible = False
Worksheets("tables").Visible = False

Else


If Application.UserName = "ALLIEPC" Then
Worksheets("Justin Steffens").Visible = True
Worksheets("Matt Johnson").Visible = True
Worksheets("tables").Visible = True

End If
End If


End Sub




Private Sub Auto_Close()




On Error Resume Next


Worksheets("tables").Visible = xlSheetVeryHidden
Worksheets("Matt Johnson").Visible = xlSheetVeryHidden
Worksheets("Justin Steffens").Visible = xlSheetVeryHidden




End Sub

 

[AllisonMartin]

Anyone out there with any suggestions?

 

[ITWare2008]

Hello Allison,

As far as I know, excel does provide a password for preventing opening an excel file or a password to prevent changing cells contents.
I don't know anything about protecting every single sheet with a different password.
Maybe - it may sound silly - if you deconstruct your workbook in as many workbooks as users and protect it with a different password for every one in order to prevent users opening workbooks different from the one assigned to them.

Hope this help

Vândalo

 

[AllisonMartin]

Thanks Vândalo.

That's sort of how I ended up with the VBA that I had posted. Thinking if I could at least hide the non relevant sheets to that person, then that should essentially take care of the need. Do you know what I messed up in the above VBA for it to not work?

 

[ITWare2008]

Hi Allison,

the structure of an if statement is

if condition then
action
elseif condition2 then
action2
else (the action below will execute if the above conditions are not met)
action3
end if

you have

if
else
if
end if
end if

this does not work at all.

Hope to clarify.

Vândalo

 

[AllisonMartin]

Thanks! That did help clarify!!