txtBox/VLOOKUP
 

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

txtBox/VLOOKUP
 

In the combo click event

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

--
HTH

Bob Phillips

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

"CMcK" wrote in message
...
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

txtBox/VLOOKUP
 

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 Phillips" wrote in message
...
In the combo click event

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

--
HTH

Bob Phillips

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

"CMcK" wrote in message
...
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

txtBox/VLOOKUP
 

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

"Bob Phillips" wrote:

In the combo click event

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

--
HTH

Bob Phillips

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

"CMcK" wrote in message
...
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

txtBox/VLOOKUP
 

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

"Mike Fogleman" wrote:

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 Phillips" wrote in message
...
In the combo click event

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

--
HTH

Bob Phillips

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

"CMcK" wrote in message
...
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

txtBox/VLOOKUP
 

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

Mike F
"CMcK" wrote in message
...
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

"Mike Fogleman" wrote:

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 Phillips" wrote in message
...
In the combo click event

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

--
HTH

Bob Phillips

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

"CMcK" wrote in message
...
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

txtBox/VLOOKUP
 

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

--
HTH

Bob Phillips

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

"CMcK" wrote in message
...
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

"Bob Phillips" wrote:

In the combo click event

With Me
.txtBox.Text =

Application.Vlookup(.cboBox.Value,Worksheets("Shee t1").Range(A2:B20),2,False
)
End With

--
HTH

Bob Phillips

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

"CMcK" wrote in message
...
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