Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Limit input to list of usernames

  1. #1
    New Member
    Join Date
    Jun 2016
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Limit input to list of usernames

    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.

  2. #2
    New Member
    Join Date
    Jun 2016
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Limit input to list of usernames

    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.

  3. #3
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    3,747
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Limit input to list of usernames

    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

  4. #4
    New Member
    Join Date
    Jun 2016
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Limit input to list of usernames

    Thank you, all working (once I figured out the Windows name and Excel name is different)

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com