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
 

Fluff

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,569
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
30,569
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
30,569
Office Version
365
Platform
Windows
Change it to a click event instead of a change event.
 

Forum statistics

Threads
1,078,521
Messages
5,340,942
Members
399,399
Latest member
SravanaSandhya

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top