Password Protect Worksheet Based on User w/ VBA

cwn7499

New Member
Joined
Jan 25, 2012
Messages
8
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.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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"
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

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
Back
Top