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

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,742
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

davesexcel

Well-known Member
Joined
Feb 26, 2006
Messages
1,089
VBA Code:
    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

Well-known Member
Joined
Apr 11, 2012
Messages
10,301
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.
Rich (BB code):
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

Well-known Member
Joined
Feb 24, 2014
Messages
2,742
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,500
Messages
5,636,690
Members
416,935
Latest member
Atulcp

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
Top