Use textbox input and VLookup to update another textbox on same userform

**gcoug** · 05-12-2011, 12:53 PM

I'm trying to use a textbox entry on a userform to populate another textbox (or label) on the same form based on a Vlookup to a dynamic named range. I've tried doing this a number of times and ways with the Vlookup worksheetfunction and all resulted in a "1004" error. I would like to populate CanType from Can and GrossWt from Truck (named ranges CanInfo and TruckInfo). Overall goal is to drive most of the database values from code and not using lookups in individual cells - file size bloat.

Need push in right (or at least better) direction.

**mojo249** · 05-12-2011, 06:32 PM

Because Can and Truck are integers on the LookUpLists sheet, you need to convert the values of the textboxes to integers (using CInt).

Try this:

Private Sub cmdEnter_Click()

On Error Resume Next

If Me.txtCan.Text = "" Then
    MsgBox "Please enter a Can.", vbCritical
    Me.txtCan.SetFocus
    Exit Sub
End If
If Me.txtTruck.Text = "" Then
    MsgBox "Please enter a Truck.", vbCritical
    Me.txtTruck.SetFocus
    Exit Sub
End If

Me.txtCanType.Text = Application.VLookup(CInt(Me.txtCan.Text), Range("CanInfo"), 3, 0)
Me.txtGW.Text = Application.VLookup(CInt(Me.txtTruck.Text), Range("TruckInfo"), 3, 0)

End Sub

Dion

**gcoug** · 05-13-2011, 12:49 AM

Works great! Thank you!

Now I can finish the data entry form and merge it with the larger dataset and other modules. Next pint is on me.

**royUK** · 05-13-2011, 04:18 AM

I wouldn't use VLOOKUP in this situation, Excel has a VBA method called .Find.

However, to avoid user error why not use CombBoxes for Can & Truck? This would improve input & make the rest simpler

'---------------------------------------------------------------------------------------
' Module    : UserForm1
' Author    : Roy Cox
' Website   : for more examples and Excel Consulting
' Date      : 13/05/2011
' Purpose   : Add information to UserForm based on CombBox selection
'---------------------------------------------------------------------------------------

Option Explicit


Private Sub cboCan_Change()
    Me.txtCanType.Value = Range("caninfo").Cells(Me.cboCan.ListIndex + 1, 3)
End Sub

Private Sub cboTruck_Change()
    With Me
        .txtGW.Value = Range("TruckInfo").Cells(.cboTruck.ListIndex + 1, 3)
        .txtNW.Value = Range("TruckInfo").Cells(.cboTruck.ListIndex + 1, 4)
        .txtTons.Value = Range("TruckInfo").Cells(.cboTruck.ListIndex + 1, 5)
        .txtTonsOU.Value = Range("TruckInfo").Cells(.cboTruck.ListIndex + 1, 6)
    End With
End Sub


Private Sub cmdExit_Click()    '
' exit userform without saving
    Unload Me
End Sub


Private Sub UserForm_Initialize()
' enter startup values
    With Me
        .txtDate.Value = Format(Now(), "mm/dd/yyyy")
        .cboSite.ListIndex = 0
        .cboTruck.List = Range("Trucks").Value
        .cboCan.List = Range("caninfo").Columns(1).Value
    End With
End Sub