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.

 

[Katy I]

It may be easiest to just lock the sheet with a password and only give out the password to the specific admins

 

[country101]

The sheets are locked to protect the cells with formulas, but the cells that need to have info entered in them are open for editing because the supervisors have to enter the data and I dont want them to be able to unlock the cells with formulas. I know doing what I want is possible as some of our files are this way already, but IT may have done that by some other means.

 

[Katy I]

Yeah if you have the cells that you want protected locked you can go to Review, Protect Workbook and add a password so only the people with access to the password can unprotect and alter locked cells. Sorry if this isn't what you are looking for, but I can't get VBA code working for it

 

[BrianExcel]

What they described above is probably easiest.

If, however, you want a more complicated solution, you can accomplish it in 2 or 3 steps.

1. Obtain either the names or user names of the people you want to give access to.
2. Put those names in a hidden sheet, accessible only by you or the workbook owner as a "Super Admin"
3. Install a button, check box, etc., that when selected, checks the user name, compares against the list of acceptable users and either opens it or doesn't.

If you need help with the code, feel free to let me know. Or send me a private message with an email and I can send an example workbook.

Final note: LOCK YOUR CODE! It does nothing to password protect a sheet if you don't lock the VBA code so people can't access it. Otherwise they can just go into the VBA editor window and determine either (1) who the authorized users are, or (2) What the password is.

 

[country101]

Brian, can you walk me through installing a check box? Do I need an activex or the other and how do I set it up to do what you are talking about. My knowledge of excel is limited to using formulas(and not a whole lot of real complicated ones at that.).

Also, I've never heard anything about locking code.....

Thanks a lot if you have time to help me.

 

[wikus]

You can use the following to retrieve the Windows user:
<Code>
Public Declare Function GetUserName Lib "advapi32.dll" _
Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Function ReturnUserName() As String
Dim rString As String * 255, sLen As Long, tString As String
tString = ""
On Error Resume Next
sLen = GetUserName(rString, 255)
sLen = InStr(1, rString, Chr(0))
If sLen > 0 Then
tString = Left(rString, sLen - 1)
Else
tString = rString
End If
On Error GoTo 0
ReturnUserName = UCase(Trim(tString))
MsgBox ReturnUserName
End Function

</code>

 

[country101]

Where does that return the user name to? And how do I use it to allow access?

 

[vds1]

Easier way to get username of current system in VBA is,

Environ("USERNAME")

Try this in Immediate window

 

[country101]

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