Hello

I have a userform combobox which is linked to the values in column A

I want to find a way in VBA so that when an item is selected from the drop
down menu, it returns the value generated by the formula in the adjacent
cell in column B

So in example below, if I select joe, I want the value 45 put somewhere I
can use it as the input for the next vba variable.


john 25
joe 45
carol 65

Anyone got any ideas?

Many thanks

TP

You can try this. It might need some tweaking, but this is
the general gist of it.

"teepee" wrote:


Hello

I have a userform combobox which is linked to the values in column A

I want to find a way in VBA so that when an item is selected from the drop
down menu, it returns the value generated by the formula in the adjacent
cell in column B

So in example below, if I select joe, I want the value 45 put somewhere I
can use it as the input for the next vba variable.


john 25
joe 45
carol 65

Anyone got any ideas?

Many thanks

TP

Forgot to paste:

If UserForm1.ComboBox1.Value = Joe Then
MyVariable = ActiveSheet.Range("A2:A100").Find _
("Joe", LookIn:=xlValues).Offset(0, 1).Value
End If

"teepee" wrote:


Hello

I have a userform combobox which is linked to the values in column A

I want to find a way in VBA so that when an item is selected from the drop
down menu, it returns the value generated by the formula in the adjacent
cell in column B

So in example below, if I select joe, I want the value 45 put somewhere I
can use it as the input for the next vba variable.


john 25
joe 45
carol 65

Anyone got any ideas?

Many thanks

TP

"JLGWhiz" wrote in message
...
Forgot to paste:

If UserForm1.ComboBox1.Value = Joe Then
MyVariable = ActiveSheet.Range("A2:A100").Find _
("Joe", LookIn:=xlValues).Offset(0, 1).Value
End If

But won't I have to rewrite the code every time I change any of the
thousands items in column A?

Maybe you could just pick up both columns when you populate the combobox. (You
don't have to show all the columns that you pick up, either.)

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
With Me.ComboBox1
If .ListIndex < 0 Then
Beep
Else
'pick out the second column
MsgBox .List(.ListIndex, 1)
End If
End With
End Sub
Private Sub UserForm_Initialize()
Dim myRng As Range

With ThisWorkbook.Worksheets("Sheet1")
Set myRng = .Range("a1:b" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With
With Me.ComboBox1
.ColumnCount = myRng.Columns.Count
.List = myRng.Value
.ColumnWidths = "33;0"
End With

Me.CommandButton1.Caption = "Cancel"
Me.CommandButton2.Caption = "Ok"
End Sub



teepee wrote:

Hello

I have a userform combobox which is linked to the values in column A

I want to find a way in VBA so that when an item is selected from the drop
down menu, it returns the value generated by the formula in the adjacent
cell in column B

So in example below, if I select joe, I want the value 45 put somewhere I
can use it as the input for the next vba variable.

john 25
joe 45
carol 65

Anyone got any ideas?

Many thanks

TP

--

Dave Peterson

"Dave Peterson" wrote in message
...
Maybe you could just pick up both columns when you populate the combobox.
(You
don't have to show all the columns that you pick up, either.)

thanks dave. So does this go in the userform code or the combobox code?

I put it in the code to populate the combobox in the userform_initialize
procedure.

I put the code to return the second column's value in the ok button's click.

All of the code is in the userform module.



teepee wrote:

"Dave Peterson" wrote in message
...
Maybe you could just pick up both columns when you populate the combobox.
(You
don't have to show all the columns that you pick up, either.)

thanks dave. So does this go in the userform code or the combobox code?

--

Dave Peterson

"Dave Peterson" wrote in message
...
I put it in the code to populate the combobox in the userform_initialize
procedure.

I put the code to return the second column's value in the ok button's click.
All of the code is in the userform module.

I see. And how would I do it if I don't want to use command buttons but
rather automate the process.

I just want the value of the B column to be available so I can put then in
the command 'MP.CurrentPosition = (insert value of B column here)'

I have no idea what MP.CurrentPosition is and how it fits in, but you may be
able to tie into the Combobox's change event:

Private Sub Combobox1_change()
Dim myVar As Variant
With Me.ComboBox1
If .ListIndex < 0 Then
Beep
Else
'pick out the second column
myVar = .List(.ListIndex, 1)
MsgBox myVar
End If
End With
End Sub

teepee wrote:

"Dave Peterson" wrote in message
...
I put it in the code to populate the combobox in the userform_initialize
procedure.

I put the code to return the second column's value in the ok button's click.
All of the code is in the userform module.

I see. And how would I do it if I don't want to use command buttons but
rather automate the process.

I just want the value of the B column to be available so I can put then in
the command 'MP.CurrentPosition = (insert value of B column here)'

--

Dave Peterson