Use VBA to Apply various Security Levels in Workbooks

[Guest]

I have a workbook with three worksheets. i would like to protect the
worksheets from editting. But it's complicated.

upon opening there should be an option to type in one of three passwords.
1st PW will open the workbook and only show Sheet 1 - with no restrictions
2nd PW will open the workbook and only show Sheet 2 - with no restrictions
3rd PW will open the workbook and only show Sheet 3 - with no restrictions
No PW will open the workbook and show all Sheets - with restrictions;
users can on view the info and use AutoFilter.

How can I do this with VBA code?

Thanks

 

[Guest]

Private Sub Workbook_Open() ' in the thisworkbook module
Dim sh as worksheet, pword as String
pword = inputBox("Enter Password or click Cancel")
if pword = "" then
for each sh in thisworkbook.worksheets
sh.Visible = True
sh.Activate
sh.Protect Password:="StrongLikeABull"
next
elseif lcase(pword) = "huggybear" then
thisworkbook.Worksheets("Sheet 1").Visible = xlSheetvisible
for each sh in thisworkbook.Worksheets
if sh.Name <> "Sheet 1" then
sh.Visible = xlSheetHidden
end if
Next
With thisworkbook.Worksheets("Sheet 1")
if .ProtectContents then
.Unprotect "StrongLikeABull"
end if
end With
elseif lcase(pword) = "toiletpaper" then
thisworkbook.Worksheets("Sheet 2").Visible = xlSheetvisible
for each sh in thisworkbook.Worksheets
if sh.Name <> "Sheet 2" then
sh.Visible = xlSheetHidden
end if
Next
With thisworkbook.Worksheets("Sheet 2")
if .ProtectContents then
.Unprotect "StrongLikeABull"
end if
end With
elseif lcase(pword) = "triciayoung" then
thisworkbook.Worksheets("Sheet 3").Visible = xlSheetvisible
for each sh in thisworkbook.Worksheets
if sh.Name <> "Sheet 3" then
sh.Visible = xlSheetHidden
end if
Next
With thisworkbook.Worksheets("Sheet 3")
if .ProtectContents then
.Unprotect "StrongLikeABull"
end if
end With
End if
End Sub

You can play with that.

http://www.cpearson.com/excel/events.htm
for an overview of events (Chip Pearson's site)

 

[Guest]

Tom, thanks for the codes. I have copied it to VBA by using ALT+F11, then
paste the code, then i replaced all instances of "thisworkbook" with the
title of my excel workbook. I save the file and closed it. when i reopened
it nothing happened. did i miss something?

Thx, Tricia

 

[Guest]

Tom, thanks for the codes. I have copied it to VBA by using ALT+F11, then
paste the code, then i replaced all instances of "thisworkbook" with the
title of my excel workbook. I save the file and closed it. when i reopened
it nothing happened. did i miss something?

Thx, Tricia

 

[Guest]

Tom, thanks for the codes. I have copied it to VBA by using ALT+F11, then
paste the code, then i replaced all instances of "thisworkbook" with the
title of my excel workbook. I save the file and closed it. when i reopened
it nothing happened. did i miss something?

Thx, Tricia

 

[NickHK]

Tom,
Do you think those passwords would stand up to a dictionary attack ? <g>

NickHK

 

[Guest]

Did you put it in the thisworkbook module?

why change from thisworkbook?

Are events enabled when the book is opened?

 

[Guest]

Tom, I have never used VBA for Excel. I'm sorry. I should have specified
that in the beginning. I have only used it for Access.

 

[Guest]

Tom, I figured out what I was doing wrong. The code you supplied works great
and i understand each line.

Another question...I would like for the AutoFilter feature to be
active/enable with the read-only.

" No PW will open the workbook and show all Sheets - with restrictions;
users can on view the info and use AutoFilter."