Vlookup using vba with auto fill in a textbox

G

GBDavis

New Member
Joined
May 22, 2019
Messages
10
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
 
Last edited by a moderator:

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
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
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Re: need to use vlookup using vba with auto fill in a textbox

here is a screen shot of form1.
Excel 2007 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][th]
E​
[/th][th]
F​
[/th][th]
G​
[/th][th]
H​
[/th][th]
I​
[/th][th]
J​
[/th][th]
K​
[/th][th]
L​
[/th][th]
M​
[/th][th]
N​
[/th][th]
O​
[/th][th]
P​
[/th][th]
Q​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td=bgcolor:#000000]
Date and Time​
[/td][td=bgcolor:#000000]
Serial Number​
[/td][td=bgcolor:#000000]
Torque Setting​
[/td][td=bgcolor:#000000]
Actual Readings​
[/td][td=bgcolor:#000000]
Column1​
[/td][td=bgcolor:#000000]
Column2​
[/td][td=bgcolor:#000000]
Column3​
[/td][td=bgcolor:#000000]
Column4​
[/td][td=bgcolor:#000000]
Actual Readings5​
[/td][td=bgcolor:#000000]
Column6​
[/td][td=bgcolor:#000000]
Column7​
[/td][td=bgcolor:#000000]
Column8​
[/td][td=bgcolor:#000000]
Column9​
[/td][td=bgcolor:#000000]
Average Reading​
[/td][td=bgcolor:#FF0000]
Pass/Fail​
[/td][td=bgcolor:#00B0F0]
Operator's Initials​
[/td][td=bgcolor:#808080] Notes[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td=bgcolor:#CCFFCC]
​
[/td][td=bgcolor:#FFFFFF]
​
[/td][td=bgcolor:#CCFFCC]
​
[/td][td=bgcolor:#CCFFCC]
1st Reading​
[/td][td=bgcolor:#CCFFCC]
2nd Reading​
[/td][td=bgcolor:#CCFFCC]
3rd Reading​
[/td][td=bgcolor:#CCFFCC]
4th Reading​
[/td][td=bgcolor:#CCFFCC]
5th Reading​
[/td][td=bgcolor:#C0C0C0]
1a Reading:​
[/td][td=bgcolor:#C0C0C0]
2a Reading:​
[/td][td=bgcolor:#C0C0C0]
3a Reading:​
[/td][td=bgcolor:#C0C0C0]
4a Reading:​
[/td][td=bgcolor:#C0C0C0]
5a Reading:​
[/td][td=bgcolor:#CCFFCC]
​
[/td][td=bgcolor:#CCFFCC]
​
[/td][td=bgcolor:#CCFFCC]
​
[/td][td=bgcolor:#CCFFCC][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td=bgcolor:#CCFFCC]
​
[/td][td=bgcolor:#FFFFFF]
​
[/td][td=bgcolor:#CCFFCC]
​
[/td][td=bgcolor:#CCFFCC]
​
[/td][td=bgcolor:#CCFFCC]
​
[/td][td=bgcolor:#CCFFCC]
​
[/td][td=bgcolor:#CCFFCC]
​
[/td][td=bgcolor:#CCFFCC]
​
[/td][td=bgcolor:#C0C0C0]
​
[/td][td=bgcolor:#C0C0C0]
​
[/td][td=bgcolor:#C0C0C0]
​
[/td][td=bgcolor:#C0C0C0]
​
[/td][td=bgcolor:#C0C0C0]
​
[/td][td=bgcolor:#CCFFCC]
​
[/td][td=bgcolor:#CCFFCC]
​
[/td][td=bgcolor:#CCFFCC]
​
[/td][td=bgcolor:#CCFFCC][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td]
5/22/2019 21:01​
[/td][td]
4567​
[/td][td]25 in_lbw +/- 6%[/td][td][/td][td]
25​
[/td][td]
24​
[/td][td]
26​
[/td][td]
25​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
25​
[/td][td]PASS[/td][td]GD[/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]


here is table 2:
Excel 2007 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td]
Serial Number​
[/td][td]
Torque Setting​
[/td][td]
Manufacturer Number​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td]
1234​
[/td][td]25 in/lbs +/- 6%[/td][td]2525tqn[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td]
2345​
[/td][td]35 in/lbs +/- 6%[/td][td]2525tqn[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td]
3456​
[/td][td]55 in/lbs +/- 6%[/td][td]2525tqn[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td]
4567​
[/td][td]65 in/lbs +/- 6%[/td][td]2525tqn[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Serial Numbers[/td][/tr][/table]


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:
<font face=Calibri><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><SPAN style="color:#007F00">' With thanks to Patrick O'Beirne</SPAN><br><SPAN style="color:#007F00">' Taken from here: https://sysmod.wordpress.com/2011/11/24/dictionary-vba-class-update/</SPAN><br><br><SPAN style="color:#007F00">'Unrestricted class just to hold pairs of values together and permit Dictionary object updating</SPAN><br><SPAN style="color:#00007F">Public</SPAN> key <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Public</SPAN> value <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN></FONT>

hope this helps.
 
Upvote 0
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.
Click to expand...

You must change "Sheet2" by the name of your sheet
 
Upvote 0
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.
Click to expand...

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.
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Excel VBA Print Name Tag from Userform
Replies
2
Views
326
Rhodie72
Rhodie72
K
  • Question Question
Help Needed
2
Replies
13
Views
291
kwavic
K
I
  • Question Question
Advice to remove old code for a new code
Replies
2
Views
192
ipbr21054
I
D
  • Question Question
Open 1 UserForm then open another UserForm
Replies
4
Views
400
dharv3y78
D
P
  • Question Question
Copy data from textbox and Combobox into Listbox
Replies
1
Views
378
Patriot2879
P

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

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
Back
Top