Results 1 to 8 of 8

Thread: Use Vlookup(?) in VBA forms
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Oct 2018
    Posts
    69
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Use Vlookup(?) in VBA forms

    Hello all,

    I got an userform named userform2 that displays Code and Productname in labels.

    On this form is a button that passes through this Code and Productname;


    Code:
    Private Sub UserForm_Initialize()
    
    lblGelCodeR.Caption = UserForm2.commonVariable
    lblProductNameR.Caption = UserForm2.commonVariable1
    This form is named; GelBMRProperties.

    Now i would like that i can use the Code on this forum to look into worksheet "Products" and return values that are behind the corresponding Code number.

    For example lblGelCodeR.Caption = 1000

    In worksheet "Products" in column A it says 1000 and and column B it says 11. I would like to see that "11" back onto my userform.
    I thought something with Vlookup but i don't know how to use that in VBA.

    Can someone assist me?

  2. #2
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,647
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Use Vlookup(?) in VBA forms

    This is how i would do it using vlookup:

    Code:
    x = Application.VLookup(lblGelCodeR.Caption, Sheets("Products").Columns("A:B"), 2, 0)
    If Not IsError(x) Then TextBox1.Text = x
    You may need to convert a number to a true number so it may fail.

  3. #3
    Board Regular
    Join Date
    Oct 2018
    Posts
    69
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Use Vlookup(?) in VBA forms

    Hey,

    Thanks for your reply. The code doesn't work.

    I also tried the follow but it errors as result;

    Code:
    Private Sub UserForm_Initialize()
    lblPage.Caption = Application.VLookup(lblGelCodeR.Caption, Sheets("Products").Columns("A:B"), 2, 0)
    End Sub
    Last edited by Bandito1; Jun 16th, 2019 at 09:52 AM.

  4. #4
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,647
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Use Vlookup(?) in VBA forms

    Probably because its numeric as suggested. Try a little change as so:

    Code:
    myLookup = lblGelCodeR.Caption
    If IsNumeric(myLookup) Then myLookup = Val(myLookup)
    x = Application.VLookup(myLookup, Sheets("Products").Columns("A:B"), 2, 0)
    If Not IsError(x) Then TextBox1.Text = x

  5. #5
    Board Regular
    Join Date
    Oct 2018
    Posts
    69
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Use Vlookup(?) in VBA forms

    Thanks once again for your reply.

    My knowledge of VBA is not that great.

    The caption that should change is lblPage.

    I changed that in the code but it doesn't return anything.

    Code:
    Private Sub UserForm_Initialize()myLookup = lblGelCodeR.Caption
    If IsNumeric(myLookup) Then myLookup = Val(myLookup)
    x = Application.VLookup(myLookup, Sheets("Products").Columns("A:B"), 2, 0)
    If Not IsError(x) Then lblPage.Caption = x
    End Sub

  6. #6
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,143
    Post Thanks / Like
    Mentioned
    60 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Use Vlookup(?) in VBA forms

    When are the controls on the form being populated?
    If posting code please use code tags.

  7. #7
    Board Regular
    Join Date
    Oct 2018
    Posts
    69
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Use Vlookup(?) in VBA forms

    Hey,

    I will redo my question, maybe it's becomes more clear / easier.

    I got a userform named "Production Returns". On this is a label called "lblGelCodeR"
    lblGelCodeR.caption displays a code like 0001.
    On this forum is a link that opens a new userform called "GelBMRProperties".
    the link contains the following code;

    Code:
    Private Sub lblInfo_Click()  With GelBMRProperties
          .lblGelCodeR.Caption = Productionreturns.lblGelCodeR.Caption
          .lblProductNameR = Productionreturns.lblProductNameR.Caption
          .Show
       End With
    End Sub
    The userform "GelBMRProperties" also contains a label named lblGelCodeR. The caption of this label is the same as the caption of lblGelCodeR on the userform "Production Returns".
    On this userform i would like to use something like Vlookup.

    The caption of lblGelCodeR (for example 0001) corresponds with the worksheet "Products". In this worksheet is in column A the Gelcodes (for example 0001) and in column B the amount of pages of the document.

    Hope this makes it more clear

  8. #8
    Board Regular
    Join Date
    Oct 2018
    Posts
    69
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Use Vlookup(?) in VBA forms

    I tried to follow the following code;

    Code:
    myLookup = lblGelCodeR.Caption
    If IsNumeric(myLookup) Then myLookup = Val(myLookup)
    x = Application.VLookup(myLookup, Sheets("Products").Columns("A:B"), 2, 0)
    If Not IsError(x) Then TextBox1.Text = x
    It seems like MyLookup stays empty. Anyone knows why?

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
  •