VBA Form with Vlookup not working

ajmartin

New Member
Joined
Jan 20, 2011
Messages
9
Hi

I am building a form for data entry into Excel and I have all other form boxes working but cannot seem to get the vlookup of a response to a combobox to trigger an entry in a text box further down the form

[FONT=Segoe UI, Segoe UI Web (West European), Segoe UI, -apple-system, BlinkMacSystemFont, Roboto, Helvetica Neue, sans-serif]I am not sure how to debug it to see which bit is failing as I get no response to the vlookup when the form is filled in. Here is the VB I am using
[/FONT]


Private Sub VendorCountryBox_Change()


var1 = WorksheetFunction.VLookup(VendorSelectionBox.Value, Worksheets("Lookups").Range("VendorCountryList"), 3, False)


VendorCountryBox.Value = var1


End Sub


The vendor selection box is a combo list box and is working fine. I have put the VBA in as a private sub amongt the other change subs at the end of the VBA. Is the problem where I have placed the VBA or the lookup syntax?

Thanks
Andy
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,956
Office Version
365
Platform
Windows
Try making it a
Code:
VendorSelectionBox_Change
event instead
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,956
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

ajmartin

New Member
Joined
Jan 20, 2011
Messages
9
You're welcome & thanks for the feedback
I was just testing this and although it worked to start with the above now returns a run time error 1004 which seems to be some problem with the MS Office resource library, does this mean that the VBA form will be unstable if I release it?


Thanks
Andy
 

ajmartin

New Member
Joined
Jan 20, 2011
Messages
9
I was just testing this and although it worked to start with the above now returns a run time error 1004 which seems to be some problem with the MS Office resource library, does this mean that the VBA form will be unstable if I release it?


Thanks
Andy
it stops at

Private Sub VendorSelectionBox_Change()

var1 = WorksheetFunction.VLookup(VendorSelectionBox.Value, Worksheets("Lookups").Range("VendorCountryList"), 3, False)


VendorCountryBox.Value = var1


End Sub

But it worked yesterday and I have not changed anything!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,956
Office Version
365
Platform
Windows
How about
Code:
Private Sub VendorSelectionBox_Change()
   Dim Var1 As Variant
   Var1 = Application.vlookup(VendorSelectionBox.Value, Worksheets("Lookups").Range("VendorCountryList"), 3, False)
   If Not IsError(Var1) Then
      VendorCountryBox.Value = Var1
   Else
      MsgBox VendorSelectionBox.Value & " Could not be found"
   End If
End Sub
 

ajmartin

New Member
Joined
Jan 20, 2011
Messages
9
How about
Code:
Private Sub VendorSelectionBox_Change()
   Dim Var1 As Variant
   Var1 = Application.vlookup(VendorSelectionBox.Value, Worksheets("Lookups").Range("VendorCountryList"), 3, False)
   If Not IsError(Var1) Then
      VendorCountryBox.Value = Var1
   Else
      MsgBox VendorSelectionBox.Value & " Could not be found"
   End If
End Sub
Ah ok so that works but I get the 'Could Not Be Found' Message every time
Something is not right, it returns the result, copies the correct data to the sheet and then i think as the form returns ready for the next entry it gives the message " Could not be found", I must have it in the wrong place

I might have taken on too much here, is there something that needs to be added to the lookup so it does not immediately look for the match when the form clears for the next entry?

Thanks
Andy
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,956
Office Version
365
Platform
Windows
Change it to a click event instead of a change event.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,867
Messages
5,489,363
Members
407,686
Latest member
Chuck1960

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top