Validation of input against the active directory

[Hii Sing Chung]

A spreadsheet contains a column of valid AD user names. I would like to
validate the user's input of these names against the active directory user
account 'display name' property after user key-in data into this column.
Sample data:
Time Server Login Name Login Reason
07:01 SS233 Username 123 M20080410000321
12:40 SS1728 Username 234 Remedy 434343
16:24 MM213 Username 244 Trouble 042323
How can I use a macro to detect changes in Login Name column and if
corresponding
Server column cell is not empty, then lookup in the AD, validate if the
spelling of
Login Name entered is correct?

 

[ryguy7272]

Right-click a tab and paste this code into the window that opens:
Function MyUserName() As String
MyUserName = Environ("UserName")
End Function

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("$A$1:$BB$4000")) Is Nothing Then
Application.EnableEvents = False
Application.ScreenUpdating = False
With Worksheets("Sheet2")
..Select
..Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
ActiveCell.Value = Target.Address
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Target.Value
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Now()
ActiveCell.NumberFormat = "mm/dd/yy"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = MyUserName()
Application.EnableEvents = True
Application.ScreenUpdating = True
End With
End If
End Sub


That should get you started.

HTH,
Ryan---

 

[Hii Sing Chung]

Thank you.

Can I add some codes to let it automatically enter the corresponding
'Entered By' cell with the AD 'display name'of the current login user (the
person who is doing the data entry into Excel at that point in time)
whenever a value is entered into the cell at Server column?
Is the Macro only be available to a particular worksheet(With
Worksheets("Sheet2"))? Can it be made available to all worksheets (or simply
the current active worksheet) except the last one?

Time Server Login Name Login Reason Entered By
07:01 SS233 Username 123 M20080410000321 User961128
12:40 SS1728 Username 234 Remedy 434343 User961122
16:24 MM213 Username 244 Trouble 042323 User961123