Employee name auto-complete on card scan Im currently running an employee name substitution on card scan.
Overall the code takes the entry trough the gun scan, &quot;scan&quot; trough the hidden reference sheet to find a match and then input the actual complete name.
I had to implement that feature to my code so that names are always written the same (for futur data treatment purpose).
If y'all have some tip, hint or other proposition to optimise the code.
One of my goal would be for it to auto-fill with any entry (right now mostly working with operator numbers) or to suggest entries based on what's written in real time in a scrolling menu (if it's not too much).
Here's my codes :
Sub Workbook_sheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.EnableEvents = False
Application.ScreenUpdating = False
If Not Intersect(Target, Sh.Range(&quot;C5:C8&quot;)) Is Nothing Then 'If change in &quot;no.employé&quot; cells

Call Employe ' Call name standardization macro

End If
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Sub Employe()
' Standardization of the operator name macro
' The code automaticly enters the employee names by is number entry
' Therefore permits employee to scan their pass and get their full names automaticly

Dim ash As Worksheet
Set ash = ActiveSheet ' Identify active sheet 
Dim positionInitCaseNom1enY As Long: positionInitCaseNom1enY = 5 'First &quot;Name&quot; cell row position
Dim positionCaseNomEnX As Long: positionCaseNomEnX = 3           '&quot;Name&quot; cells culumn position
Dim k As long: k = positionInitCaseNom1enY                    ' Counter incrément position
Dim no As Variant  ' Value of name cell (changes every loop)
Dim i As Integer   ' Row integer

    For i = 0 To 3
    k = k + i       ' Up a row (first 0)
    no = ash.Cells(k, positionCaseNomEnX).Value ' Gives &quot;no&quot; the current &quot;Name&quot; cell value
    
        If no &lt;&gt; &quot;&quot; Then   ' Vérify if theres something in &quot;Name&quot; cell
        Dim nos As String  
        nos = CStr(no)     ' no to string (now nos)
        Dim valeurnorm As Range
        Set valeurnorm = Sheets(&quot;Liste Employé&quot;).Range(&quot;A2:A200&quot;).Find(nos)
        ' Looking for a match in name reference sheet
        
            If valeurnorm Is Nothing Then ' If no match found
                If MsgBox(&quot;Entrer un numéro d'employé valide&quot; &amp; vbNewLine &amp; &quot;Le numéro d'employé entrée est le suivant: &quot; &amp; nos, 5 + vbCritical, &quot;Erreur&quot;) = vbRetry Then
                ' Msgbox that the employee entered a false number and to retry

                    ash.Cells(k, positionCaseNomEnX).Value = &quot;&quot; ' Clear old number
                    ash.Cells(k, positionCaseNomEnX).Select     ' Reselect cases

                End If
            Else ' If name is in the reference sheet

                ash.Cells(k, positionCaseNomEnX).Value = valeurnorm ' Change the value of the entry for the complete name
            End If
            
        End If
  Next
End Sub

Sorry for all of the typos, i need to translate everything and my written english is kinda s***.
positionInitCaseNom1enY and positionCaseNomEnX should be declared as Const or in an Enum because they are default values that never change.
Const positionInitCaseNom1enY As Long = 5
Const positionCaseNomEnX As Long = 3

Do to implicit conversion no does not need to be cast to a String.  The compiler automatically does it for you.
Dim nos As String  
nos = CStr(no)     ' no to string (now nos)

Use dynamic ranges whenever possible.
Set valeurnorm = Sheets(&quot;Liste Employé&quot;).Range(&quot;A2:A200&quot;).Find(nos)

Assuming the name list is the only thing in column A, you should dynamically size you range to fit the data like this:

With Sheets(&quot;Liste Employé&quot;)
    Set valeurnorm = .Range(&quot;A2&quot;, .Cells(.Rows.Count, &quot;A&quot;).End(xlUp)).Find(nos)
End With



Using Dynamic Named Ranges helps to give your code identity.  And that is what we want to do as developers.  If you have to comeback a year from now, you may not know what StandarValues represent but you will immediately understand what EmployeeNames are.

Set employeeNames= Sheets(&quot;Liste Employé&quot;).Range(&quot;EmployeeNames&quot;).Find(nos)

Alerts are necessary but having to click a MsgBox() can get irritating.  I would prefer that:

If .Find(&quot;Another Solution&quot;) is Not Nothing Then Call
Use(.Find(&quot;Another Solution&quot;))

Solution 1: Use Conditional Formatting
How To Highlight Cells If Not In Another Column In Excel?
Solution 2: Use an ActiveX Combobox
Setup
• Insert a hidden ComboBox on each Worksheet that needs the name validation
• Give it a meaning full name
• Set it's ListFillRange a Dynamic Named Range of employee names

When a cell in the validation range is selected use the Workbook_SheetSelectionChange()
• Move the ComboBox over the Activecell
• REsize the ComboBox to fit the ActiveCell
• Set the ComboBox.LinkedCell = ActiveCell
• Set focus to the ComboBox
When the user selects a cell in the
• Hide the ComboBox
• Set the ComboBox.LinkedCell = Nothing


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    AdjustEmployeeNameComboBox Sh, Target
End Sub

Private Sub AdjustEmployeeNameComboBox(ByVal Sh As Object, ByVal Target As Range)
    Dim hasEmployeeRange As Boolean
    On Error Resume Next
    
    Dim EmployeeNameComboBox As OLEObject
    Set EmployeeNameComboBox = Sh.OLEObjects(&quot;EmployeeNameComboBox&quot;)
    hasEmployeeRange = Err.Number = 0
    On Error GoTo 0

    If Not hasEmployeeRange Then Exit Sub
    
    Dim isEmployeeRangeSelected As Boolean
    isEmployeeRangeSelected = Not Intersect(Target, Sh.Range(&quot;C5:C8&quot;)) Is Nothing
    
    With EmployeeNameComboBox
        .LinkedCell = &quot;&quot;
        .Visible = isEmployeeRangeSelected
    End With
    
    If Target.Cells.Count &gt; 1 Then Exit Sub
    
    If isEmployeeRangeSelected Then
        With EmployeeNameComboBox
            .LinkedCell = Target.Address
            .Top = Target.Top
            .Left = Target.Left
            .Height = Target.Height
            .Width = Target.Width
            .Activate
        End With
    End If
End Sub


Ugh..kinda nasty but it works.
Solution 3: Almost the Same as 2
Use the same setup as Solution2 but use a Cell Style to indicate that it is an Employee Name Validation Range.  The advantage of this is that you could have the validation anywhere on the Worksheet without having to update your code.
For this solution you will need to change

isEmployeeRangeSelected = Not Intersect(Target, Sh.Range(&quot;C5:C8&quot;)) Is Nothing

to this

isEmployeeRangeSelected = Target.Style = &quot;Cell Style Name goes Here&quot;