Validating user name in cell

My name is Harish..

I am very new to VBA

I have a quick.. and hopefully simple question.

I have a worksheet that is public (in our workplace).. anyone can open it and log entries.. but only two users are allowed to "close" the entries (individual lines)

I have a column called "Closed by" that has a Data Validation range.. the range has two usernames. (the names of the two that are allowed to close the entries)

I have a function that can call the username of the current session ( =username()) and return that user name

What I need to do is, if some one selects a user from the drop down for the "closed by" column, I want to check if the current user is the user from the drop down... if it is.. then allow the selection.. else pop up a message box to say that they are not authorised to complete that entry.

I hope my question makes sense..

Look forward to your reply.

Kind regards

Harish

Hi Harish.

You will need to copy the following code to the appropriate Worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim uName As String
    Dim closeColNum As Integer
    
    'Call your UDF to return the current user's username
    uName = username
    'Change this to the column number appropriate for your "Closed By" column
    closeColNum = 2
    
    'Only check if the "Closed By" column has been changed
    If Target.Column = closeColNum Then
        If Target.Value <> uName Then
            'Username and closed by value don't match
            MsgBox "Sorry " & uName & ", you are not authorised to close this entry."
            
            'Disable events so that cell can be emptied without triggering this macro again
            Application.EnableEvents = False
            'Reset the cell value to blank
            Target.Value = ""
            'Re-enable events so that future changes trigger this macro
            Application.EnableEvents = True
        End If
    End If
End Sub

This macro assumes that the "Closed By" column is column 2 (Column "B"), but change this within the code to the appropriate column for your situation.

Hope this helps

Hi Ajryan88,

That is awesome.. does exactly what I need it to do. Thank you very much for your help and the super quick response.

Much appreciated.

Kind regards

Harish

No worries, don't forget to add some reputation and mark the thread as solved.

Happy to help