how to make user name access to spreadsheet?

[country101]

How would I make it so that only certain users in our network could access my spreadsheet to edit and all others would only have read only access? I have about 10 supervisors that need access for each spreadsheet. All the other supervisors and officers need to be able to look to see what is completed, but dont need to edit. I already have a macro that protects each sheet upon close so that cells with formulas dont accidentally get changed and it is not accidentally left unprotected. Could somebody help me out with a macro to pick out the user names that need access and make it read only for everybody else? This would allow the supervisors quick access to input the qual dates, but the protection would keep them from messing up formulas inadvertantly. The Admin(s) would be the only one that would need to get in and change any of the formulas and make any major changes to the sheet.

 

[vds1]

Ok, but how do I use this in relation to limiting access and how will this return then name to be used??

Goto VBA Environment (ALT +F11)

Goto Project Explorer

Double click on Thisworkbook Object

Paste this code

Change sheet name you want to protect and its password

Private Sub Workbook_Open()
'You got to change sheet name and password
'Assume Restricted Access is for sheet named "Sheet1" - Password -123
Dim StrAdmin() As Variant
Sheet1.Protect "123"
'Mention all user machine name you want to provide access
StrAdmin = Array("vds1", "user2", "user3", "user4", "user5", "user6", "user7", "user8", "user9", "User10")
For Lp = 0 To UBound(StrAdmin)
  If StrAdmin(Lp) = Environ("USERNAME") Then
    Sheet1.Unprotect "123"
    Exit Sub
  End If
Next Lp
End Sub

 

[country101]

Ok, I understand how to input the code, but I have a few questions about the code itself for learning purposes and clarification.

I am assuming that any text preceded by ' is just a note for me that does not need to be included. Is this correct?

In the array for user names, what is vds1? I am asking this in case I change the number of users and such so I know how everything needs to be laid out.

I like the sheet protection the way it is, but could this be changed to protect the workbook instead? If I understand it right, if the workbook was protected it would all only be read only, unless the username was correct and then each sheet would be protected from the supervisors that are not admins so that formulas couldnt get changed. If that needed to be done, a password could be entered as this will be infrequent.

 

[vds1]

I am assuming that any text preceded by ' is just a note for me that does not need to be included. Is this correct?

Yes.

In the array for user names, what is vds1? I am asking this in case I change the number of users and such so I know how everything needs to be laid out.

Its My username. You can remove and add appropriate username.

I like the sheet protection the way it is, but could this be changed to protect the workbook instead? If I understand it right, if the workbook was protected it would all only be read only, unless the username was correct and then each sheet would be protected from the supervisors that are not admins so that formulas couldnt get changed. If that needed to be done, a password could be entered as this will be infrequent.

</SPAN>

Below code will password protect all sheets if user is not mentioned in the array. It is as good as read Only file.

Private Sub Workbook_Open()</SPAN>
'You got to change sheet name and password</SPAN>
'Assume Restricted Access is for sheet named "Sheet1" - Password -123</SPAN>
Dim StrAdmin() As Variant</SPAN>
Dim Ws As Worksheet</SPAN>
'Password Protect All sheets</SPAN>
For Each Ws In ThisWorkbook.Sheets</SPAN>
  Ws.Protect "123"</SPAN>
Next Ws</SPAN>
'Mention all user machine name you want to provide access</SPAN>
StrAdmin = Array("vds1", "user2", "user3", "user4", "user5", "user6", "user7", "user8", "user9", "User10")</SPAN>
For Lp = 0 To UBound(StrAdmin)</SPAN>
  If StrAdmin(Lp) = Environ("USERNAME") Then</SPAN>
      For Each Ws In ThisWorkbook.Sheets</SPAN>
         Ws.Unprotect "123"</SPAN>
      Next Ws</SPAN>
      Exit Sub</SPAN>
  End If</SPAN>
Next Lp</SPAN>
End Sub</SPAN>

</SPAN>

 

[country101]

I have allowed users to edit ranges on the sheets though so that the names and dates can be edited. These will still be unprotected for everybody if left this way, wont they?

 

[country101]

anybody?

 

[vds1]

Well in this case, you got to go for "Allow users for edit ranges". This option is found in Review Tab in excel 2007 and 2010. You can provide title for a range , set the range and provide a password. Below you would find Permissions button, where in you could add people you want to provide access to and click on allow check mark which indicates that a user can edit range without password. Complex task , yet works wonder.

 

[country101]

ok, I remember seeing the permissions tab. Will that take thier windows logon?

 

[country101]

I just tried your code and when I compliled it, it gave me an error saying "ambiguous name detected: Workbook_open". How do I fix this?

I am also getting a run time error 1004 saying the password I supplied is not correct. here is my code:

Private Sub Workbook_Open()
Me.Sheets("Brown").Activate
ActiveSheet.Range("d5").Activate
Me.Sheets("Carroll").Activate
ActiveSheet.Range("d5").Activate
Me.Sheets("Felkins").Activate
ActiveSheet.Range("d5").Activate
Me.Sheets("Jackson").Activate
ActiveSheet.Range("d5").Activate
Me.Sheets("Roe").Activate
ActiveSheet.Range("d5").Activate
Me.Sheets("Tudor").Activate
ActiveSheet.Range("d5").Activate
Me.Sheets("Wright").Activate
ActiveSheet.Range("d5").Activate
Me.Sheets("Data").Activate
ActiveSheet.Range("A1").Activate
Me.Sheets("Alpha Crew").Activate
ActiveSheet.Range("d5").Activate
Dim StrAdmin() As Variant
Dim Ws As Worksheet
For Each Ws In ThisWorkbook.Sheets
Ws.Protect "alpha"
Next Ws
StrAdmin = Array("bscarbo", "jbrow32", "jcarro1", "kfelkin", "mjacks8", "sroe", "ctudor", "kwrigh2", "user9", "User10")
For Lp = 0 To UBound(StrAdmin)
If StrAdmin(Lp) = Environ("USERNAME") Then
For Each Ws In ThisWorkbook.Sheets
Ws.Unprotect "alpha"
Next Ws
Exit Sub
End If
Next Lp
End Sub

 

[country101]

disregard about the error. I had previous protection on there. Once I cleared it, it works fine.

 

[country101]

I think I am making this too complicated and I dont think this is doing what I need it to. I am going to start a new thread. VDS1, please look it up if you dont mind continuing to help me. I will call it "Protection and user editing". Thanks for all the help so far. I have atleast learned from your input and my mistakes.