VBA to let user input his own id a and change it to a name

[tonywatsonhelp]

Hi Everyone,
Firstly I know this wont stop people if they want to hack the document but thats not the point

I have excel documents that have to get signed of by managers by staff.
Its a simple cell that says "Managers approval signature" then they type ther name into a cell,
the problem with this is its just a little too easy for someone just to tyoe the name in,
So what id like to do is this.
I have a sheet Called "Control" which is very hidden
This Sheet holds the ID codes In Column A and the Names in Column B

So with sheets "Orders" we have a cell "L66" and the Managers must put in there Code, when its entered the macro takes that code and find the name in column B then puts that name into cell "L66" and locks the cell

So simply put,
Manager has to type ID into L66,
macro trys to find whats typed in sheet "Control" Column A,
if it can not find anything it clears the cell and says "User ID Not Recognised!"
If it finds a match it takes the name in column B and puts it into L66 and locks that cell.


please help if you can

Thanks

Tony

 

[davesexcel]

    Dim sh As Worksheet, ws As Worksheet
    Dim nm As String, rng As Range
    Set sh = Sheets("Orders")
    Set ws = Sheets("Control")

    nm = sh.Range("L66").Value

    With ws
        Set rng = .Columns(1).Find(nm)
        If Not rng Is Nothing Then
            sh.Range("L66").Value = rng.Offset(, 1)
        Else: MsgBox "You Suck"
        End If
    End With

End Sub

 

[mumps]

Different approach:
Start by unlocking all the cells in your "Order" sheet and then protect the sheet with a password of your choosing. Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your "Order" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Change "MyPassWord" in the code (in red-two occurrences) to match your chosen password. Close the code window to return to your sheet. Make an entry in L66 and press the RETURN key.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("L66")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    ActiveSheet.Unprotect Password:="MyPassword"
    Dim fnd As Range
    Set fnd = Sheets("Control").Range("A:A").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not fnd Is Nothing Then
        Target = fnd.Offset(, 1)
        Target.Locked = True
    Else
        Target.ClearContents
        Target.Select
        MsgBox ("Invalid ID.  Please try again.")
    End If
    With ActiveSheet
        .Protect Password:="MyPassword"
        .EnableSelection = xlUnlockedCells
    End With
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[tonywatsonhelp]

Firstly a big thank you to davesexcel, this is what i had in mind and will be very helpfull.
secondly thank you again to mumps, this is great and very clever, thanks for the help
Tony

 

[mumps]

Glad to help.