Greetings everyone. I'm not sure if I can do this, but on a user form I have
a combo box that populates from a range on sheet 'DATA', with items. There
is also a text box that I want to find the price of that item. Thoretically,
assuming my data is such that item is in column A of 'DATA', starting at row
18, and price is in column B of 'DATA', how can I get the price of the item
selected from the combo box. I found a post by Dave Peterson to populate the
list of the combo, but I am at a loss as to how to populate the text box for
price. Any ideas? Below is the code to populate the list(thanks Dave).

Private Sub UserForm_Initialize()
With Worksheets("Data")
Me.cboFuel1.List = .Range("A18", .Cells(.Rows.Count,
"A").End(xlUp)).Value
End With

Greg

Try this

Private Sub UserForm_Initialize()
With Worksheets("Data")
Me.cboFuel1.List = .Range("A18", _
.Cells(.Rows.Count, "A").End(xlUp)).Value
End With
End Sub

Private Sub cboFuel1_Change()
With Worksheets("Data")
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set DataRange = .Range("A18", "A" & Lastrow)
SelectedItem = Me.cboFuel1.Value
Set c = DataRange.Find(what:=SelectedItem, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
TextBoxdata = c.Offset(0, 1)
End If

End With
End Sub



"Greg Snidow" wrote:

Greetings everyone. I'm not sure if I can do this, but on a user form I have
a combo box that populates from a range on sheet 'DATA', with items. There
is also a text box that I want to find the price of that item. Thoretically,
assuming my data is such that item is in column A of 'DATA', starting at row
18, and price is in column B of 'DATA', how can I get the price of the item
selected from the combo box. I found a post by Dave Peterson to populate the
list of the combo, but I am at a loss as to how to populate the text box for
price. Any ideas? Below is the code to populate the list(thanks Dave).

Private Sub UserForm_Initialize()
With Worksheets("Data")
Me.cboFuel1.List = .Range("A18", .Cells(.Rows.Count,
"A").End(xlUp)).Value
End With

Greg

Try the below which is the combo change event...which will lookup the
corresponding value in ColB and populate that to me.textbox1

Private Sub cboFuel1_Change()
Me.TextBox1 = WorksheetFunction.VLookup(Me.ComboBox1, _
Worksheets("Data").Range("A:B"), 2, 0)
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"Greg Snidow" wrote:

Greetings everyone. I'm not sure if I can do this, but on a user form I have
a combo box that populates from a range on sheet 'DATA', with items. There
is also a text box that I want to find the price of that item. Thoretically,
assuming my data is such that item is in column A of 'DATA', starting at row
18, and price is in column B of 'DATA', how can I get the price of the item
selected from the combo box. I found a post by Dave Peterson to populate the
list of the combo, but I am at a loss as to how to populate the text box for
price. Any ideas? Below is the code to populate the list(thanks Dave).

Private Sub UserForm_Initialize()
With Worksheets("Data")
Me.cboFuel1.List = .Range("A18", .Cells(.Rows.Count,
"A").End(xlUp)).Value
End With

Greg

Typo...
comboname is cboFuel1
textbox name is TextBox1

Private Sub cboFuel1_Change()
Me.TextBox1 = WorksheetFunction.VLookup(Me.cboFuel1, _
Worksheets("Data").Range("A:B"), 2, 0)
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Try the below which is the combo change event...which will lookup the
corresponding value in ColB and populate that to me.textbox1

Private Sub cboFuel1_Change()
Me.TextBox1 = WorksheetFunction.VLookup(Me.ComboBox1, _
Worksheets("Data").Range("A:B"), 2, 0)
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"Greg Snidow" wrote:

Greetings everyone. I'm not sure if I can do this, but on a user form I have
a combo box that populates from a range on sheet 'DATA', with items. There
is also a text box that I want to find the price of that item. Thoretically,
assuming my data is such that item is in column A of 'DATA', starting at row
18, and price is in column B of 'DATA', how can I get the price of the item
selected from the combo box. I found a post by Dave Peterson to populate the
list of the combo, but I am at a loss as to how to populate the text box for
price. Any ideas? Below is the code to populate the list(thanks Dave).

Private Sub UserForm_Initialize()
With Worksheets("Data")
Me.cboFuel1.List = .Range("A18", .Cells(.Rows.Count,
"A").End(xlUp)).Value
End With

Greg

Jacob, I hit the "yes" button, for whether or not this post answered the
question, but for some reason yours is not showing up. I just wanted to let
you know I did not omit your solution. Maybe its a bug?

"Jacob Skaria" wrote:

Typo...
comboname is cboFuel1
textbox name is TextBox1

Private Sub cboFuel1_Change()
Me.TextBox1 = WorksheetFunction.VLookup(Me.cboFuel1, _
Worksheets("Data").Range("A:B"), 2, 0)
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Try the below which is the combo change event...which will lookup the
corresponding value in ColB and populate that to me.textbox1

Private Sub cboFuel1_Change()
Me.TextBox1 = WorksheetFunction.VLookup(Me.ComboBox1, _
Worksheets("Data").Range("A:B"), 2, 0)
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"Greg Snidow" wrote:

Greetings everyone. I'm not sure if I can do this, but on a user form I have
a combo box that populates from a range on sheet 'DATA', with items. There
is also a text box that I want to find the price of that item. Thoretically,
assuming my data is such that item is in column A of 'DATA', starting at row
18, and price is in column B of 'DATA', how can I get the price of the item
selected from the combo box. I found a post by Dave Peterson to populate the
list of the combo, but I am at a loss as to how to populate the text box for
price. Any ideas? Below is the code to populate the list(thanks Dave).

Private Sub UserForm_Initialize()
With Worksheets("Data")
Me.cboFuel1.List = .Range("A18", .Cells(.Rows.Count,
"A").End(xlUp)).Value
End With

Greg

hi
add something like this to your combo box change event
Private Sub Cbo1_Change()
Dim cbv As String
Dim r As Range
cbv = Me.Cbo1.Value
Set r = Range("A18:B24")
Me.TextBox1.Value = WorksheetFunction.VLookup(cbv, r, 2)

regards
FSt1
End Sub

"Greg Snidow" wrote:

Greetings everyone. I'm not sure if I can do this, but on a user form I have
a combo box that populates from a range on sheet 'DATA', with items. There
is also a text box that I want to find the price of that item. Thoretically,
assuming my data is such that item is in column A of 'DATA', starting at row
18, and price is in column B of 'DATA', how can I get the price of the item
selected from the combo box. I found a post by Dave Peterson to populate the
list of the combo, but I am at a loss as to how to populate the text box for
price. Any ideas? Below is the code to populate the list(thanks Dave).

Private Sub UserForm_Initialize()
With Worksheets("Data")
Me.cboFuel1.List = .Range("A18", .Cells(.Rows.Count,
"A").End(xlUp)).Value
End With

Greg

make the combox columncount = 2 and set the BoundColumn =2

set the source to both columns A and B of your data


if you don't want to see the value of B in the combobox, then set its column
width to zero

now, when you click an item (change) the value returned will be the value
thats in the 2nd column


"Greg Snidow" wrote:

Greetings everyone. I'm not sure if I can do this, but on a user form I have
a combo box that populates from a range on sheet 'DATA', with items. There
is also a text box that I want to find the price of that item. Thoretically,
assuming my data is such that item is in column A of 'DATA', starting at row
18, and price is in column B of 'DATA', how can I get the price of the item
selected from the combo box. I found a post by Dave Peterson to populate the
list of the combo, but I am at a loss as to how to populate the text box for
price. Any ideas? Below is the code to populate the list(thanks Dave).

Private Sub UserForm_Initialize()
With Worksheets("Data")
Me.cboFuel1.List = .Range("A18", .Cells(.Rows.Count,
"A").End(xlUp)).Value
End With

Greg

You can create a combobox that has multiple columns (and even hide the second
column from view) and then just get the second column's value from the
combobox--never go back to the worksheet.

Option Explicit
Private Sub ComboBox1_Change()
With Me.ComboBox1
If .ListIndex < 0 Then
'nothing chosen, clear the textbox
Me.TextBox1.Value = ""
Else
Me.TextBox1.Value = .List(.ListIndex, 1)
End If
End With
End Sub
Private Sub UserForm_Initialize()
Dim myRng As Range

With Worksheets("Data")
Set myRng = .Range("A18:B" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With

With Me.ComboBox1
.ColumnCount = myRng.Columns.Count
.List = myRng.Value
.ColumnWidths = "100;0" 'hide the second column
End With
End Sub


Greg Snidow wrote:

Greetings everyone. I'm not sure if I can do this, but on a user form I have
a combo box that populates from a range on sheet 'DATA', with items. There
is also a text box that I want to find the price of that item. Thoretically,
assuming my data is such that item is in column A of 'DATA', starting at row
18, and price is in column B of 'DATA', how can I get the price of the item
selected from the combo box. I found a post by Dave Peterson to populate the
list of the combo, but I am at a loss as to how to populate the text box for
price. Any ideas? Below is the code to populate the list(thanks Dave).

Private Sub UserForm_Initialize()
With Worksheets("Data")
Me.cboFuel1.List = .Range("A18", .Cells(.Rows.Count,
"A").End(xlUp)).Value
End With

Greg

--

Dave Peterson

Wow, thank you all so much for the good solutions, all of them work very
well. Coincidentally, I messed around with it last night, and got it to work
in a much less elegant fashion. Below is the code, which will promptly be
deleted. One question I have about my version, is that I could not set the
LstRow variable by any means I found here. I know it is a simple syntax
error.

Dim StartCellAddress As String
Dim CurrentCellString As String
Dim LookupValue As Double
StartCellAddress = Sheets("data").Range("A2").Address
CurrentCellString = Sheets("data").Range(StartCellAddress).Value

Dim LstRow As Integer
'LstRow = Sheets("data").Cells(.Rows.Count, "A").End(xlUp).Row
LstRow = 13
Dim MyCell As String
Dim Rng As Range
Set Rng = Sheets("data").Range("A2:A" & LstRow)

LookupValue = Sheets("data").Range(StartCellAddress).Offset(0, 1).Value
For n = 1 To LstRow
If Me.cboFuel1.Value = CurrentCellString Then
Me.txtPrc1.Value = LookupValue
Exit For
Else
StartCellAddress = Sheets("data").Range("A2").Offset(n, 0).Address
CurrentCellString = Sheets("data").Range(StartCellAddress).Value
LookupValue = Sheets("data").Range(StartCellAddress).Offset(0,
1).Value
End If
Next

"Greg Snidow" wrote:

Greetings everyone. I'm not sure if I can do this, but on a user form I have
a combo box that populates from a range on sheet 'DATA', with items. There
is also a text box that I want to find the price of that item. Thoretically,
assuming my data is such that item is in column A of 'DATA', starting at row
18, and price is in column B of 'DATA', how can I get the price of the item
selected from the combo box. I found a post by Dave Peterson to populate the
list of the combo, but I am at a loss as to how to populate the text box for
price. Any ideas? Below is the code to populate the list(thanks Dave).

Private Sub UserForm_Initialize()
With Worksheets("Data")
Me.cboFuel1.List = .Range("A18", .Cells(.Rows.Count,
"A").End(xlUp)).Value
End With

Greg