VBA Form with Vlookup not working

[ajmartin]

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]

Try making it a

VendorSelectionBox_Change

event instead

 

[ajmartin]

Try making it a

VendorSelectionBox_Change

event instead

thank you! Many hours were spent looking looking for that one!

 

[Fluff]

You're welcome & thanks for the feedback

 

[ajmartin]

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]

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]

How about

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]

How about

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]

Change it to a click event instead of a change event.

 

[ajmartin]

Change it to a click event instead of a change event.

thanks again, I am learning as I go and you have been very helpful!