Userform combobox question

  • Thread starter Thread starter teepee
  • Start date Start date
T

teepee

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
 
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
 
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.
 
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
 
run time error '-2147024809 (80070057)
could not get the list property. invalid argument.

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

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.
 
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.
 
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?
 
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

It could be lots of things.

Can you be more specific about which line causes the error?
 
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 = ""
 
Back
Top