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:
Sort by date Sort by votes
Re: need to use vlookup using vba with auto fill in a textbox

If you capture the serial number directly in column B of sheet1, then put the following code in the events of your sheet1.


Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B:B")) Is Nothing Then
        If Target.Count > 1 Then Exit Sub
        If Target.Value = "" Then Exit Sub
        Set f = Sheets("Sheet2").Range("A:A").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
        If Not f Is Nothing Then
            MsgBox "The serial already exists"
        Else
            MsgBox "Does not exist"
        End If
    End If
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.
 
Upvote 0

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
You must log in or register to reply here.

Similar threads

P
  • Question
Copy data from textbox and Combobox into Listbox
Replies
1
Views
187
Patriot2879
P
D
  • Solved
TextBox looping help required in VBA
Replies
3
Views
386
DanteAmor
DanteAmor
I
  • Solved
Show or Hide Textbox advice
Replies
8
Views
426
dmt32
D
R
  • Question
Msg box for
Replies
1
Views
95
Micron
M
X
  • Question
userform auto fill all
Replies
1
Views
260
Micron
M

Forum statistics

Threads
1,214,983
Messages
6,122,595
Members
449,089
Latest member
Motoracer88

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