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

run time error '-2147024809 (80070057)
could not get the list property. invalid argument.

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

myVar = .List(.ListIndex, 1)

is the offending line in the debugger
Office 2007

"teepee" wrote in message
...
myVar = .List(.ListIndex, 1)

is the offending line in the debugger

and it also says 'can't get the list property. Invalid argument.' when I
hover the cursor over .list

It worked for me.

Did you make any changes to the other code?

If you did, you may want to post that code.

teepee wrote:

"teepee" wrote in message
...
myVar = .List(.ListIndex, 1)

is the offending line in the debugger

and it also says 'can't get the list property. Invalid argument.' when I
hover the cursor over .list

--

Dave Peterson

"Dave Peterson" wrote in message
...
It worked for me.

Did you make any changes to the other code?

If you did, you may want to post that code.


Hmm the other code refers to command buttons. I put both in verbatim (just
changing the formula addresses) but then it won't let me launch the userform
at all but calls a bug at UserForm2.Show

In the userform

Private Sub UserForm_Initialize()
Dim myRng As Range

With ThisWorkbook.Worksheets("graphs")
Set myRng = .Range("fa1:fb" & .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.CommandButton18.Caption = "Cancel"
Me.CommandButton19.Caption = "Ok"
End Sub

and

Option Explicit
Private Sub CommandButton18_Click()
Unload Me
End Sub
Private Sub CommandButton19_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

and in the combobox code

Private Sub Combobox1_change()



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



End If
End With

MP.CurrentPosition = myVar

End Sub

But I still have no idea what MP.CurrentPosition is. Until you share that, I'm
not sure how to help.

teepee wrote:

"Dave Peterson" wrote in message
...
It worked for me.

Did you make any changes to the other code?

If you did, you may want to post that code.

Hmm the other code refers to command buttons. I put both in verbatim (just
changing the formula addresses) but then it won't let me launch the userform
at all but calls a bug at UserForm2.Show

In the userform

Private Sub UserForm_Initialize()
Dim myRng As Range

With ThisWorkbook.Worksheets("graphs")
Set myRng = .Range("fa1:fb" & .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.CommandButton18.Caption = "Cancel"
Me.CommandButton19.Caption = "Ok"
End Sub

and

Option Explicit
Private Sub CommandButton18_Click()
Unload Me
End Sub
Private Sub CommandButton19_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

and in the combobox code

Private Sub Combobox1_change()

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

End If
End With

MP.CurrentPosition = myVar

End Sub



--

Dave Peterson

"Dave Peterson" wrote in message
...
But I still have no idea what MP.CurrentPosition is. Until you share that,
I'm
not sure how to help.

mp.currentposition won't have any impact on the rest of the code - I just
included it for completeness. The userform has an embedded media player and
mp.currentposition = 10 would tell it to jump to 10 seconds into a video or
audio.

It could be lots of things.

Can you be more specific about which line causes the error?

teepee wrote:

"Dave Peterson" wrote in message
...
But I still have no idea what MP.CurrentPosition is. Until you share that,
I'm
not sure how to help.

mp.currentposition won't have any impact on the rest of the code - I just
included it for completeness. The userform has an embedded media player and
mp.currentposition = 10 would tell it to jump to 10 seconds into a video or
audio.

--

Dave Peterson

In this case it's where I invoke the userform itself

Sub startt()
UserForm2.Show
End Sub

I get

run time error 70
permission denied

but it does launch if I take out

Private Sub UserForm_Initialize()

Dim myRng As Range

With ThisWorkbook.Worksheets("graphs")
Set myRng = .Range("fa1:fb" & .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.CommandButton18.Caption = "Cancel"
Me.CommandButton19.Caption = "Ok"


End Sub

and replace it with

Private Sub UserForm_Initialize()

End Sub

"Dave Peterson" wrote in message
...
It could be lots of things.

Can you be more specific about which line causes the error?

teepee wrote:

"Dave Peterson" wrote in message
...
But I still have no idea what MP.CurrentPosition is. Until you share
that,
I'm
not sure how to help.

mp.currentposition won't have any impact on the rest of the code - I just
included it for completeness. The userform has an embedded media player
and
mp.currentposition = 10 would tell it to jump to 10 seconds into a video
or
audio.

--

Dave Peterson

Further investigation shows that it is the presence of the line

..List = myRng.Value

that is causing the userform to not launch

Maybe you assigned the rowsource to the combobox.

You could either remove that assignment in the properties window for that
combobox (while you're in the VBE).

Or you could clean it up in code:

Add a single line right after the with statement:

With Me.ComboBox1
.RowSource = ""



teepee wrote:

In this case it's where I invoke the userform itself

Sub startt()
UserForm2.Show
End Sub

I get

run time error 70
permission denied

but it does launch if I take out

Private Sub UserForm_Initialize()

Dim myRng As Range

With ThisWorkbook.Worksheets("graphs")
Set myRng = .Range("fa1:fb" & .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.CommandButton18.Caption = "Cancel"
Me.CommandButton19.Caption = "Ok"

End Sub

and replace it with

Private Sub UserForm_Initialize()

End Sub

"Dave Peterson" wrote in message
...
It could be lots of things.

Can you be more specific about which line causes the error?

teepee wrote:

"Dave Peterson" wrote in message
...
But I still have no idea what MP.CurrentPosition is. Until you share
that,
I'm
not sure how to help.

mp.currentposition won't have any impact on the rest of the code - I just
included it for completeness. The userform has an embedded media player
and
mp.currentposition = 10 would tell it to jump to 10 seconds into a video
or
audio.

--

Dave Peterson

--

Dave Peterson

"Dave Peterson" wrote in message
...
Maybe you assigned the rowsource to the combobox.

You could either remove that assignment in the properties window for that
combobox (while you're in the VBE).

Or you could clean it up in code:

Add a single line right after the with statement:

With Me.ComboBox1
.RowSource = ""


Bingo. It works. I'm enormously grateful.