Vlookup using vba with auto fill in a textbox

GBDavis · May 22, 2019

I am writing a spread sheet that will let the user know if a torque wrench passes or fails. I have 2 Sheets. Sheet 1 holds the information from userform1, The second sheet (sheet2), holds the serial number, torque value, and the manufacturers number. (3 columns) I have the first form (form 1) collect the information from the wrench, when the user inputs the serial number I would like it to do a vlookup on the sn and verify if it is there, if not then tell the user (msgbox) that it needs to be placed into the spreadsheet. (Sheet2)

I have everything working except the vlookup portion.

Here is the code I have:

Code:

Private Sub CommandButton2_Click()
tbdate.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
TextBox7.Value = ""
TextBox5.Value = ""
TextBox6.Value = ""
TextBox7.Value = ""
TextBox8.Value = ""
Call CMD_Cancel_Click
End Sub
Private Sub CMD_Cancel_Click()
If MsgBox("Do you really want to close?", vbOKCancel) = vbOK Then
ActiveWorkbook.Save
Application.Quit
End If
'End Sub
'Me.Hide
End Sub

Private Sub lstSN_Click()
'TextBox2 = lstSN

End Sub
Sub vlookup3()
For i = 4 To Cells(Rows.Count, "A").End(xlUp).Row
'Cells(i, "A").Value =
Next i

End Sub

Private Sub TextBox2_Change()

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, closemode As Integer)
If closemode = vbFormControlMenu Then

Cancel = True
Call CMD_Cancel_Click
End If
End Sub
Private Sub CommandButton4_Click()
Application.Visible = True
Me.Hide


End Sub

Private Sub TextBox3_Change()
    OnlyNumbers

End Sub
 Private Sub TextBox4_Change()

    OnlyNumbers

End Sub
Private Sub TextBox5_Change()

    OnlyNumbers

End Sub
Private Sub TextBox6_Change()

    OnlyNumbers

End Sub

 Private Sub TextBox7_Change()

    OnlyNumbers

End Sub
Private Sub TextBox9_Change()

    OnlyLetters

End Sub

Private Sub OnlyNumbers()



    If TypeName(Me.ActiveControl) = "TextBox" Then

        With Me.ActiveControl

            If Not IsNumeric(.Value) And .Value <> vbNullString Then

                MsgBox "Sorry, only numbers allowed"

                .Value = vbNullString

            End If

        End With

    End If

    

End Sub

Private Sub OnlyLetters()



    If TypeName(Me.ActiveControl) = "TextBox" Then

        With Me.ActiveControl

            If IsNumeric(.Value) And .Value <> vbNullString Then

                MsgBox "Sorry, only Letters allowed"

                .Value = vbNullString

            End If

        End With

    End If

    

End Sub
Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Trim(TextBox3.Value) = "" And Me.Visible Then
MsgBox ("Please enter a value in the text box."), vbCritical, "Enter"
Cancel = True
Me.TextBox3.SetFocus
TextBox3.BackColor = vbYellow
Else
TextBox3.BackColor = vbWhite
End If
End Sub

Private Sub TextBox4_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Trim(TextBox4.Value) = "" And Me.Visible Then
MsgBox ("Please enter a value in the text box."), vbCritical, "Enter"
Cancel = True
Me.TextBox4.SetFocus
TextBox4.BackColor = vbYellow
Else
TextBox4.BackColor = vbWhite
End If
End Sub
Private Sub TextBox5_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Trim(TextBox5.Value) = "" And Me.Visible Then
MsgBox ("Please enter a value in the text box."), vbCritical, "Enter"
Cancel = True
Me.TextBox5.SetFocus
TextBox5.BackColor = vbYellow
Else
TextBox5.BackColor = vbWhite
End If
End Sub
Private Sub TextBox6_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Trim(TextBox6.Value) = "" And Me.Visible Then
MsgBox ("Please enter a value in the text box."), vbCritical, "Enter"
Cancel = True
Me.TextBox6.SetFocus
TextBox6.BackColor = vbYellow
Else
TextBox6.BackColor = vbWhite
End If
End Sub
Private Sub TextBox7_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Trim(TextBox7.Value) = "" And Me.Visible Then
MsgBox ("Please enter a value in the text box."), vbCritical, "Enter"
Cancel = True
Me.TextBox7.SetFocus
TextBox7.BackColor = vbYellow
Else
TextBox7.BackColor = vbWhite
End If
End Sub
Private Sub TextBox9_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Trim(TextBox9.Value) = "" And Me.Visible Then
MsgBox ("Please enter a value in the text box."), vbCritical, "Enter"
Cancel = True
Me.TextBox9.SetFocus
TextBox9.BackColor = vbYellow
Else
TextBox9.BackColor = vbWhite
End If
End Sub
Private Sub UserForm_Initialize()
Worksheets("Sheet1").Activate
Me.tbdate = Now
Label6.BackColor = vbYellow
Label6.Font.Size = 14
'TextBox6.Font.Bold
Label6.Caption = "INCOMPLETE"

End Sub
Private Sub CommandButton1_Click()

Application.Visible = False
'MsgBox ("App is Hidden"), vbOKCancel
Dim unusedRow As Long
Dim x As Long
Dim y As Long
Dim avz As Double
Dim a As Double
Dim b As Double
Dim c As Double
Dim d As Double
Dim e As Double
Dim k As String





'eRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
'
eRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Sheet1.Cells(eRow, 1).Value = Now 'TextBox1.Value
Sheet1.Cells(eRow, 7).Value = TextBox3.Value

a = TextBox3.Value
'MsgBox (a), vbOKCancel
Sheet1.Cells(eRow, 5).Value = TextBox7.Value
b = TextBox7.Value
'MsgBox (b), vbOKCancel
Sheet1.Cells(eRow, 6).Value = TextBox5.Value
c = TextBox5.Value
Sheet1.Cells(eRow, 7).Value = TextBox6.Value
d = TextBox6.Value
Sheet1.Cells(eRow, 8).Value = TextBox7.Value
e = TextBox7.Value

avz = (a + b + c + d + e) / 5
TextBox8.Value = avz
Sheet1.Cells(eRow, 16).Value = TextBox9.Value

' MsgBox (avz), vbOKCancel

Sheet1.Cells(eRow, 14).Value = TextBox8.Value


' Choice of 25,35 55, or 65 in_lb torque
'Sheet1.Cells(eRow, 3) = ActiveCell
If OptionButton1.Value = True Then
Sheet1.Cells(eRow, 3).Value = "25 in_lbw +/- 6%"
k = 25
    If avz > 23.5 Then pass_fail = "PASS"
    If avz < 26.5 Then pass_fail = "PASS"
    If avz < 23.5 Then pass_fail = "FAIL"
    If avz > 26.5 Then pass_fail = "FAIL"
    
GoTo pass_fail_show

  
ElseIf OptionButton2.Value = True Then
Sheet1.Cells(eRow, 3).Value = "35 in_lbw +/- 6%"
k = 35
    If avz > 32.9 Then pass_fail = "PASS"
    If avz < 37.1 Then pass_fail = "PASS"
    If avz < 32.9 Then pass_fail = "FAIL"
    If avz > 37.1 Then pass_fail = "FAIL"
    GoTo pass_fail_show
    
ElseIf OptionButton3.Value = True Then
Sheet1.Cells(eRow, 3).Value = "55 in_lbw +/- 6%"
k = 55
    If avz > 51.7 Then pass_fail = "PASS"
    If avz < 58.3 Then pass_fail = "PASS"
    If avz < 51.7 Then pass_fail = "FAIL"
    If avz > 58.3 Then pass_fail = "FAIL"
    GoTo pass_fail_show
ElseIf OptionButton4.Value = True Then
Sheet1.Cells(eRow, 3).Value = "65 in_lbw +/- 6%"
k = 65
    If avz > 61.1 Then pass_fail = "PASS"
    If avz < 68.9 Then pass_fail = "PASS"
    If avz < 61.1 Then pass_fail = "FAIL"
    If avz > 68.9 Then pass_fail = "FAIL"
    GoTo pass_fail_show
End If

' for 25 in lb pass or fail
'MsgBox (pass_fail), vbOKCancel
'MsgBox (k), vbOKCancel

pass_fail_show:
If pass_fail = "PASS" Then GoTo passing
If pass_fail = "fail" Then GoTo failing

passing:
If pass_fail = "FAIL" Then GoTo failing
For Count = 1 To 10
Label6.BackColor = vbGreen
Label6.Font.Size = 14
'TextBox6.Font.Bold
Label6.Caption = "PASS"
Next Count
GoTo ending

failing:

For Count = 1 To 10
Label6.BackColor = vbRed
Label6.Font.Size = 14
'TextBox6.Font.Bold
Label6.Caption = "FAIL"
Next Count
GoTo ending
ending:
eRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row - 1
Sheet1.Cells(eRow, 2).Value = lstSN.Value
TextBox2.Value = lstSN.Value


btm:
'MsgBox (eRow1), vbOKCancel
'MsgBox ("check the spread sheet")
Sheet1.Cells(eRow, 15).Value = pass_fail


Application.Visible = True
'MsgBox ("App is Not Hidden"), vbOKCancel
MsgBox ("Ready to clear this Form"), vbYesNo
'verifiy all fields filled
If TextBox2.Value = "" Then
MsgBox ("you missed box2"), vbOKCancel
ElseIf TextBox3.Value = "" Then
MsgBox ("you missed box3"), vbOKCancel
ElseIf TextBox7.Value = "" Then
MsgBox ("you missed box7"), vbOKCancel
ElseIf TextBox5.Value = "" Then
MsgBox ("you missed box5"), vbOKCancel
ElseIf TextBox6.Value = "" Then
MsgBox ("you missed box6"), vbOKCancel
ElseIf TextBox7.Value = "" Then
MsgBox ("you missed box7"), vbOKCancel
ElseIf TextBox8.Value = "" Then
MsgBox ("you missed box8"), vbOKCancel
ElseIf TextBox9.Value = "" Then
MsgBox ("you missed box9"), vbOKCancel

End If
' clear date field

tbdate.Value = ""

'clear all the option buttons

OptionButton1.Value = False
OptionButton2.Value = False
OptionButton3.Value = False
OptionButton4.Value = False

' clear all the txt boxes

TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
TextBox5.Value = ""
TextBox6.Value = ""
TextBox7.Value = ""
TextBox8.Value = ""
TextBox9.Value = ""

Label6.BackColor = vbYellow
Label6.Font.Size = 14
'TextBox6.Font.Bold
Label6.Caption = "INCOMPLETE"

End Sub
Private Sub CommandButton3_Click()
' load the new serial number input form
UserForm1.Hide
UserForm2.Show

End Sub

any help would be appreciated.

thank you GBDavis

DanteAmor · May 22, 2019

Re: need to use vlookup using vba with auto fill in a textbox

I guess lstSN is your control of the serial number, but I do not know if it's a textbox or a combobox.

Put the following inside your userform code

Code:

Dim closing As Boolean  '[COLOR=#ff0000]This variable goes up all the code[/COLOR]


Private Sub lstSN_Exit(ByVal Cancel As MSForms.ReturnBoolean)


    Dim sh As Worksheet
    If closing Then Exit Sub
    If lstSN.Value = "" Then Exit Sub
    Set sh = Sheets("sheet2")
    Set f = sh.Range("A:A").Find(lstSN.Value, LookIn:=xlValues, lookat:=xlWhole)
    If f Is Nothing Then
        MsgBox "The serial number does not exist"
        Cancel = True
    End If
End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    closing = True
End Sub

GBDavis · May 23, 2019

Re: need to use vlookup using vba with auto fill in a textbox

it is a list box. Should i use a combo or text box?

DanteAmor · May 23, 2019

Re: need to use vlookup using vba with auto fill in a textbox

How do you fill the listbox?
I guess you fill it with sheet2, so if it's in the listbox it's on sheet2, if so, why do you want to find the data on sheet2?

GBDavis · May 23, 2019

Re: need to use vlookup using vba with auto fill in a textbox

I placed the code you sent me and I get an run-time error '9' sub script out of range for the line 'Set Sh = Sheets("sheet2"). If you would like I could try and send you my spread sheet and forms? please let me know.

GBDavis · May 23, 2019

Re: need to use vlookup using vba with auto fill in a textbox

I am using sheet 2 because it is where the user inputs the serial numbers originaly and do not want to duplicate it.

GBDavis · May 23, 2019

Re: need to use vlookup using vba with auto fill in a textbox

here is a screen shot of form1.
Excel 2007 32 bit

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Date and Time	Serial Number	Torque Setting	Actual Readings	Column1	Column2	Column3	Column4	Actual Readings5	Column6	Column7	Column8	Column9	Average Reading	Pass/Fail	*Operator's Initials*	Notes
2				1st Reading	2nd Reading	3rd Reading	4th Reading	5th Reading	1a Reading:	2a Reading:	3a Reading:	4a Reading:	5a Reading:
3
4	5/22/2019 21:01	4567	25 in_lbw +/- 6%		25	24	26	25						25	PASS	GD

Sheet: Sheet1

here is table 2:
Excel 2007 32 bit

	A	B	C
1	Serial Number	Torque Setting	Manufacturer Number
2	1234	25 in/lbs +/- 6%	2525tqn
3	2345	35 in/lbs +/- 6%	2525tqn
4	3456	55 in/lbs +/- 6%	2525tqn
5	4567	65 in/lbs +/- 6%	2525tqn

Sheet: Serial Numbers

here is the main input form:
[face=Calibri]Option Explicit
' With thanks to Patrick O'Beirne
' Taken from here: https://sysmod.wordpress.com/2011/11/24/dictionary-vba-class-update/

'Unrestricted class just to hold pairs of values together and permit Dictionary object updating
Public key As String
Public value As Variant[/face]

here is the sn input form:
Option Explicit ' With thanks to Patrick O'Beirne ' Taken from here: https://sysmod.wordpress.com/2011/11/24/dictionary-vba-class-update/ 'Unrestricted class just to hold pairs of values together and permit Dictionary object updating Public key As String Public value As Variant

hope this helps.

DanteAmor · May 23, 2019

Re: need to use vlookup using vba with auto fill in a textbox

GBDavis said:
I placed the code you sent me and I get an run-time error '9' sub script out of range for the line 'Set Sh = Sheets("sheet2"). If you would like I could try and send you my spread sheet and forms? please let me know.

You must change "Sheet2" by the name of your sheet

DanteAmor · May 23, 2019

Re: need to use vlookup using vba with auto fill in a textbox

GBDavis said:
I am using sheet 2 because it is where the user inputs the serial numbers originaly and do not want to duplicate it.

Sorry but I'm not understanding you.
You can explain again what you need.

You must explain what you have and what you expect of result, please, do not send more code, just explain with real data what you have. names of sheets, cells, names of controls.

GBDavis · May 24, 2019

Re: need to use vlookup using vba with auto fill in a textbox

I have 2 sheets. Sheet one is where the user through a input screen enters the data, ie - date and time stamp (auto using "NOW"), next they enter a serial numbe of the wrench they are testing. Next they input the test results from the test (5 separate inputs), then excel gets the average for that wrench and either shows pass or fail depending on the inputs. The operator then places their initials in the input box and presses the "ENTER" command button and then it places all the info into the spread sheet.

On the second sheet is where we place the info of the new wrench which is only 3 inputs - (Serial Number, Torque setting, and Model number) - entered from the second input screen.

What I am trying to do is when the user inputs the Serial number in while getting ready to test the wrench, is for excel to check against what is already input on sheet2 to see if it is already there and if not show a message box informing them that they must go to the new wrench serial number input screen and enter the new serial number.

for example - when I type in the serial number of a wrench I am checking I need to know if it is a new wrench or not.

I hope this helps. Please let me know.

Thank you

GBDavis

Vlookup using vba with auto fill in a textbox

GBDavis

New Member

Excel Facts

DanteAmor

Well-known Member

GBDavis

New Member

DanteAmor

Well-known Member

GBDavis

New Member

GBDavis

New Member

GBDavis

New Member

DanteAmor

Well-known Member

DanteAmor

Well-known Member

GBDavis

New Member

Similar threads

Forum statistics

Share this page

Vlookup using vba with auto fill in a textbox

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

New Member

New Member

Well-known Member

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock