PDA

View Full Version : VBA Vlookup



Emoncada
10-23-2012, 09:53 AM
I have the followin code I am trying to get to work, but no luck

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

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?

Teeroy
10-24-2012, 04:19 AM
Are you refreshing the userform after setting the values you want? Try adding DoEvents after the IF statements to refresh the form display.

Emoncada
10-24-2012, 06:18 AM
I still can't get it to work.
Is my code correct?

Teeroy
10-24-2012, 01:41 PM
I can't see anything obviously wrong. Can you post a sample workbook?