Public Guncode As String
Option Explicit

Sub Test()

Call Vlookup

End Sub

Sub Vlookup()

Dim Retrieve1 As String
Dim Retrieve2 As String
Dim FinalRow As Long
Dim FinalColumn As Long
Dim WholeRange As String

If GunTest.TextBox1 = "" Then
    Exit Sub
If GunTest.TextBox1 <> "" Then

    MsgBox Guncode
End If
End If

With Sheets(1)

FinalRow = Range("A65536").End(xlUp).Row
FinalColumn = Range("IV1").End(xlToLeft).Column
WholeRange = "A2:" & CStr(FinalColumn) & CStr(FinalRow)

Retrieve1 = Application.WorksheetFunction.Vlookup(Trim(Guncode), Range(WholeRange), 1, False) 'Locate specific tool according to QR code number
Retrieve2 = Application.WorksheetFunction.Vlookup(Trim(Guncode), Range(WholeRange), 5, False) 'Locate specific gun type according to QR code number

If Guncode = "" Then
    MsgBox "This gun doesn't exist in database!"
    Else
    MsgBox "The tool number is:" & Retrieve1 & vbCrLf & "The gun type is:" & Retrieve2
    End If
End With


End Sub

Option Explicit

Private Sub Label1_Click()

End Sub

Private Sub CommandButton1_Click()

If TextBox1 = "" Then Exit Sub 'Set condition 1 of exiting the program

Guncode = GunTest.TextBox1

With Me

    Call Module1.Test

End With



End Sub

Private Sub PartID_Click()

End Sub

Private Sub TextBox1_Change()

End Sub

Private Sub UserForm_Click()

End Sub