Password Protection

AllisonMartin

New Member
Joined
Dec 5, 2013
Messages
31
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!!
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

AllisonMartin

New Member
Joined
Dec 5, 2013
Messages
31
This is what I started with, but it's not fully working, so there is something I'm missing.

Private Sub Auto_Open()


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
 

ITWare2008

Board Regular
Joined
Apr 16, 2010
Messages
174
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

New Member
Joined
Dec 5, 2013
Messages
31

ADVERTISEMENT

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

Board Regular
Joined
Apr 16, 2010
Messages
174
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,564
Messages
5,596,875
Members
414,106
Latest member
Tigretto

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top