Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: VBA Form with Vlookup not working
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jan 2011
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Form with Vlookup not working

    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

    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



    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

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,252
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA Form with Vlookup not working

    Try making it a
    Code:
    VendorSelectionBox_Change
    event instead
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Jan 2011
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Form with Vlookup not working

    Quote Originally Posted by Fluff View Post
    Try making it a
    Code:
    VendorSelectionBox_Change
    event instead
    thank you! Many hours were spent looking looking for that one!

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,252
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA Form with Vlookup not working

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    New Member
    Join Date
    Jan 2011
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Form with Vlookup not working

    Quote Originally Posted by Fluff View Post
    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

  6. #6
    New Member
    Join Date
    Jan 2011
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Form with Vlookup not working

    Quote Originally Posted by ajmartin View Post
    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!

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,252
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA Form with Vlookup not working

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #8
    New Member
    Join Date
    Jan 2011
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Form with Vlookup not working

    Quote Originally Posted by Fluff View Post
    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

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,252
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA Form with Vlookup not working

    Change it to a click event instead of a change event.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #10
    New Member
    Join Date
    Jan 2011
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Form with Vlookup not working

    Quote Originally Posted by Fluff View Post
    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!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •