Password Protect Worksheet Based on User w/ VBA

[cwn7499]

I have a spreadsheet with various formulas that I would like to protect from others editing. If I use tools protection, it's annoying because once I apply the protection, I have to unprotect before I can edit it again.

I would like to use VBA to enter in a script that reads the user name (environment) variable "username" and based on users I enter in, will allow editing of certain cells on various worksheets within a workbook.

I've searched all day for code that would work for me...but haven't been successful yet.

Thanks for your help!

I can email spreadsheet to you if that will help.

 

[cwn7499]

Below is the code I've been trying to use

But I get a Runtime '9' Error - Script out of Range

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Sheets("Sheet1").Protect Password:="Password"
Sheets("Sheet2").Protect Password:="Password"
Sheets("Sheet3").Protect Password:="Password"
Sheets("Sheet4").Protect Password:="Password"
Sheets("Sheet5").Protect Password:="Password"
Sheets("Sheet6").Protect Password:="Password"
Sheets("Sheet7").Protect Password:="Password"
Sheets("Sheet8").Protect Password:="Password"
Sheets("Sheet9").Protect Password:="Password"
Sheets("Sheet10").Protect Password:="Password"
Sheets("Sheet11").Protect Password:="Password"
Sheets("Sheet12").Protect Password:="Password"
Sheets("Sheet13").Protect Password:="Password"
Sheets("Sheet14").Protect Password:="Password"
Sheets("Sheet15").Protect Password:="Password"
Sheets("Sheet16").Protect Password:="Password"
Sheets("Sheet17").Protect Password:="Password"
Sheets("Sheet18").Protect Password:="Password"
Sheets("Sheet19").Protect Password:="Password"
Sheets("Sheet20").Protect Password:="Password"
Sheets("Sheet21").Protect Password:="Password"
Sheets("Sheet22").Protect Password:="Password"
Sheets("Sheet23").Protect Password:="Password"

Sheet1.Visible = xlSheetVisible
Sheet2.Visible = xlSheetVeryHidden
Sheet3.Visible = xlSheetVeryHidden
Sheet4.Visible = xlSheetVeryHidden
Sheet5.Visible = xlSheetVeryHidden
Sheet6.Visible = xlSheetVeryHidden
Sheet7.Visible = xlSheetVeryHidden
Sheet8.Visible = xlSheetVeryHidden
Sheet9.Visible = xlSheetVeryHidden
Sheet10.Visible = xlSheetVeryHidden
Sheet11.Visible = xlSheetVeryHidden
Sheet12.Visible = xlSheetVeryHidden
Sheet13.Visible = xlSheetVeryHidden
Sheet14.Visible = xlSheetVeryHidden
Sheet15.Visible = xlSheetVeryHidden
Sheet16.Visible = xlSheetVeryHidden
Sheet17.Visible = xlSheetVeryHidden
Sheet18.Visible = xlSheetVeryHidden
Sheet19.Visible = xlSheetVeryHidden
Sheet20.Visible = xlSheetVeryHidden
Sheet21.Visible = xlSheetVeryHidden
Sheet22.Visible = xlSheetVeryHidden
Sheet23.Visible = xlSheetVeryHidden
ThisWorkbook.Save

ActiveWorkbook.Close True

End Sub

Private Sub Workbook_Open()
Dim userName As String

userName = Environ("Username")

Select Case userName

Case "Ebenezer"

Sheet1.Visible = xlSheetVisible
Sheet2.Visible = xlSheetVisible
Sheet3.Visible = xlSheetVisible
Sheet4.Visible = xlSheetVisible
Sheet5.Visible = xlSheetVisible
Sheet6.Visible = xlSheetVisible
Sheet7.Visible = xlSheetVisible
Sheet8.Visible = xlSheetVisible
Sheet9.Visible = xlSheetVisible
Sheet10.Visible = xlSheetVisible
Sheet11.Visible = xlSheetVisible
Sheet12.Visible = xlSheetVisible
Sheet13.Visible = xlSheetVisible
Sheet14.Visible = xlSheetVisible
Sheet15.Visible = xlSheetVisible
Sheet16.Visible = xlSheetVisible
Sheet17.Visible = xlSheetVisible
Sheet18.Visible = xlSheetVisible
Sheet19.Visible = xlSheetVisible
Sheet20.Visible = xlSheetVisible
Sheet21.Visible = xlSheetVisible
Sheet22.Visible = xlSheetVisible
Sheet23.Visible = xlSheetVisible

Sheets("Sheet1").Unprotect Password:="Password"
Sheets("Sheet2").Unprotect Password:="Password"
Sheets("Sheet3").Unprotect Password:="Password"
Sheets("Sheet4").Unprotect Password:="Password"
Sheets("Sheet5").Unprotect Password:="Password"
Sheets("Sheet6").Unprotect Password:="Password"
Sheets("Sheet7").Unprotect Password:="Password"
Sheets("Sheet8").Unprotect Password:="Password"
Sheets("Sheet9").Unprotect Password:="Password"
Sheets("Sheet10").Unprotect Password:="Password"
Sheets("Sheet11").Unprotect Password:="Password"
Sheets("Sheet12").Unprotect Password:="Password"
Sheets("Sheet13").Unprotect Password:="Password"
Sheets("Sheet14").Unprotect Password:="Password"
Sheets("Sheet15").Unprotect Password:="Password"
Sheets("Sheet16").Unprotect Password:="Password"
Sheets("Sheet17").Unprotect Password:="Password"
Sheets("Sheet18").Unprotect Password:="Password"
Sheets("Sheet19").Unprotect Password:="Password"
Sheets("Sheet20").Unprotect Password:="Password"
Sheets("Sheet21").Unprotect Password:="Password"
Sheets("Sheet22").Unprotect Password:="Password"
Sheets("Sheet23").Unprotect Password:="Password"

Case "Scrooge"

Sheet1.Visible = xlSheetVisible
Sheet2.Visible = xlSheetVisible
Sheet3.Visible = xlSheetVisible
Sheet4.Visible = xlSheetVisible
Sheet5.Visible = xlSheetVisible
Sheet6.Visible = xlSheetVisible
Sheet7.Visible = xlSheetVisible
Sheet8.Visible = xlSheetVisible
Sheet9.Visible = xlSheetVisible
Sheet10.Visible = xlSheetVisible
Sheet11.Visible = xlSheetVisible
Sheet12.Visible = xlSheetVisible
Sheet13.Visible = xlSheetVisible
Sheet14.Visible = xlSheetVisible
Sheet15.Visible = xlSheetVisible
Sheet16.Visible = xlSheetVisible
Sheet17.Visible = xlSheetVisible
Sheet18.Visible = xlSheetVisible
Sheet19.Visible = xlSheetVisible
Sheet20.Visible = xlSheetVisible
Sheet21.Visible = xlSheetVisible
Sheet22.Visible = xlSheetVisible
Sheet23.Visible = xlSheetVisible

Sheets("Sheet1").Unprotect Password:="Password"
Sheets("Sheet2").Unprotect Password:="Password"
Sheets("Sheet3").Unprotect Password:="Password"
Sheets("Sheet4").Unprotect Password:="Password"
Sheets("Sheet5").Unprotect Password:="Password"
Sheets("Sheet6").Unprotect Password:="Password"
Sheets("Sheet7").Unprotect Password:="Password"
Sheets("Sheet8").Unprotect Password:="Password"
Sheets("Sheet9").Unprotect Password:="Password"
Sheets("Sheet10").Unprotect Password:="Password"
Sheets("Sheet11").Unprotect Password:="Password"
Sheets("Sheet12").Unprotect Password:="Password"
Sheets("Sheet13").Unprotect Password:="Password"
Sheets("Sheet14").Unprotect Password:="Password"
Sheets("Sheet15").Unprotect Password:="Password"
Sheets("Sheet16").Unprotect Password:="Password"
Sheets("Sheet17").Unprotect Password:="Password"
Sheets("Sheet18").Unprotect Password:="Password"
Sheets("Sheet19").Unprotect Password:="Password"
Sheets("Sheet20").Unprotect Password:="Password"
Sheets("Sheet21").Unprotect Password:="Password"
Sheets("Sheet22").Unprotect Password:="Password"
Sheets("Sheet23").Unprotect Password:="Password"

 

[cwn7499]

NEVER MIND!! After posting it...for some reason I saw it in a different light...

I have it working now.

Only 1 thing: When opening and closing the workbook, there is about a 5 second delay as it closes all sheets etc...is there a way to speed this up?

Thanks!