Consulting

Results 1 to 4 of 4

Thread: VBA Vlookup

  1. #1
    VBAX Expert
    Joined
    Apr 2007
    Location
    Orlando, FL
    Posts
    751
    Location

    VBA Vlookup

    I have the followin code I am trying to get to work, but no luck

    [VBA]Private Sub ListPart1_Change()
    Dim strMyNewVariable As String
    strMyNewVariable = Me.ListPart1.Value
    Dim Lrange As Range
    If Me.LblModel.Caption = "2780" Then
    Set Lrange = Sheets("List").Range("A3:B15")
    Mymsg = Application.WorksheetFunction.VLookup(strMyNewVariable, Lrange, 2, 0)
    Me.LblPart1Num.Caption = Mymsg
    End If

    If Me.LblModel.Caption = "2800" Then
    Set Lrange = Sheets("List").Range("D3:E17")
    Mymsg = Application.WorksheetFunction.VLookup(strMyNewVariable, Lrange, 2, 0)
    Me.LblPart1Num.Caption = Mymsg
    End If[/VBA]

    Basically I have some option buttons that when selected LblModel.caption changes to it's value, then I have Listboxes that look at that lblmodel.caption and list box values populate.

    Now what I am trying to do it have LblPart1Num.Caption which is located under each listbox get a value depending on what's selected in the listbox.
    Any idea why I am not getting any value when I select from the listbox?

  2. #2
    VBAX Mentor Teeroy's Avatar
    Joined
    Apr 2012
    Location
    Sydney, Australia
    Posts
    414
    Location
    Are you refreshing the userform after setting the values you want? Try adding DoEvents after the IF statements to refresh the form display.
    _________________________________________________________________________
    "In theory there is no difference between theory and practice. In practice there is." - Chuck Reid

    Any day you learn something new is a day not wasted.

  3. #3
    VBAX Expert
    Joined
    Apr 2007
    Location
    Orlando, FL
    Posts
    751
    Location
    I still can't get it to work.
    Is my code correct?

  4. #4
    VBAX Mentor Teeroy's Avatar
    Joined
    Apr 2012
    Location
    Sydney, Australia
    Posts
    414
    Location
    I can't see anything obviously wrong. Can you post a sample workbook?
    _________________________________________________________________________
    "In theory there is no difference between theory and practice. In practice there is." - Chuck Reid

    Any day you learn something new is a day not wasted.

Posting Permissions

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