how to make user name access to spreadsheet?

country101

Board Regular
Joined
May 31, 2012
Messages
61
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.
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Katy I

New Member
Joined
Jun 21, 2012
Messages
44
It may be easiest to just lock the sheet with a password and only give out the password to the specific admins
 

country101

Board Regular
Joined
May 31, 2012
Messages
61
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

New Member
Joined
Jun 21, 2012
Messages
44
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

Well-known Member
Joined
Apr 21, 2010
Messages
969

ADVERTISEMENT

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

Board Regular
Joined
May 31, 2012
Messages
61
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

Active Member
Joined
May 2, 2010
Messages
292

ADVERTISEMENT

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>
 

vds1

Well-known Member
Joined
Oct 5, 2011
Messages
1,194
Easier way to get username of current system in VBA is,
Code:
Environ("USERNAME")

Try this in Immediate window
 

country101

Board Regular
Joined
May 31, 2012
Messages
61
Ok, but how do I use this in relation to limiting access and how will this return then name to be used??
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,918
Members
414,416
Latest member
Nobu

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
Top