Limit input to list of usernames

Dmobbs

New Member
Joined
Jun 14, 2016
Messages
8
Hi

I have a spreadsheet that I want Team A to be able to input into one set of columns, Team B into another and Team C into a third set of columns

When writing the Macros I have been using the following line at the start of each macro. (list of names is a lot longer)

If Application.UserName <> "Jane Jones" And Application.UserName <> "Bob Smith" Then Exit Sub

Is there a way to have a sheet with the list of team A in column A and team B in column B and team C in column C then have a bit of the macro to say if UserName is not in column A then exit sub otherwise carry on?

My thinking is that this would be a lot easier to change the list of team members when staff come and go rather than running through all the macros to make sure I change each and every one.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Re read my post and thought it could be clearer.

I don't think Allow Users to Edit Ranges option would work as I want to force users to use the macros (on buttons) to input information into cells and limit which users can use which buttons rather than giving free entry options.
 
Upvote 0
Hi,
you can do what you want for all valid users by creating a list in a worksheet & then have a function validate

Also, better to use network user names by using Environ("username") This is the user name which you log into Windows with and cannot be changed by users

Add worksheet and name it Valid Users.

Enter each users network user name in required columns.

In Standard Module:

Code:
Function ValidUser(ByVal User As String) As Boolean
    Dim Users As Variant
    Dim cell As Range
    
    For Each cell In Sheets("Valid Users").Range("A1").CurrentRegion.Cells
        ValidUser = CBool(cell.Value = User)
        If ValidUser Then Exit Function
    Next cell


End Function



Then add this line in your code:

Code:
If Not ValidUser(Environ("USERNAME")) Then Exit Sub

Hope Helpful

Dave
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

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