txtBox/VLOOKUP

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,
I'm new to VB programming (but not Excel), so please bear with me.

I'm creating a usrFrm with a cboBox (Project Name) & txtBox (Project No.).
I want the txtBox to return the corresponding value depending on what's been
selected in the cboBox.
The cbo values are derived from a table on a worksheet:

Project name Project no.
a 1
b 2
etc,

Question: 1)would I need to use VLOOKUP in VB? If so, how?
2)how do I get the result of the VLOOKUP to be the value in
the txtBox?

Your help is very much appreciated.
Mac
 
In the combo click event

With Me
.txtBox.Text =
Application.Vlookup(.cboBox.Value,Worksheets("Sheet1").Range(A2:B20),2,False
)
End With

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Or in the combobox Change Event:

Private Sub ComboBox1_Change()
Me.TextBox1.Value = WorksheetFunction.VLookup _
(ComboBox1.Value, Sheet1.Range("A2:B9"), 2, False)
End Sub

Of course, change the Sheet1.Range("A2:B9") to your lokup range.

Mike F
 
Bob,

Thanks for your swift response - very much appreciated.
You had one minor error in your code - the VLOOKUP range cell reference
needed to be in speech marks, ("X:X") - otherwise it did exactly what I
wanted.

CMcK
 
Mike,

Thanks for your response. Unfortunately this code did not work. The data
is held in a worksheet called 'Projects', so instead of referencing
'sheet1.range("X:X")' I used:
worksheets.("Projects").range("X:X")

and the following error came up:

Run-time error 1004:
Unable to get the Vlookup property of the Worksheet function class

Not sure how to resolve this. Nonetheless, your efforts are genuinely
appreciated.

CMcK
 
Drop the first dot
change worksheets.("Projects").range("X:X")
to Worksheets("Projects").range("X:X")

Mike F
 
Sorry about that, that will teach me to test it <g>

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Back
Top