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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Try making it a
Code:
VendorSelectionBox_Change
event instead
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Change it to a click event instead of a change event.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

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