Selected ListBox item to TextBox

[Honnore]

Hi there dear reader...
Usually I find all responses to my Excel issues in already existing threads.
Today not. So any help will be more than welcome. The situation is as follows.


I have one UserForm with one multiple-column ListBox in it.
I would like to add the value of the third column of the selected item of the
ListBox to a TextBox located in another userform. The below code does not
work :

Private Sub ListBox1_DblClick(ByVal cancel As MSForms.ReturnBoolean)
For i = 0 To UserForm1.ListBox1.ListCount - 1
If UserForm1.ListBox1.Selected(i) = True Then
UserForm1.ListBox1.List(i, 12).AddItem UserForm2.TextBox1.Text
Next i
UserForm2.Show
End Sub

Thank you in advance for your help
Honnore

 

[Rick Rothstein]

Try this DblClick event code instead of what you posted...

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
UserForm2.TextBox1 = ListBox1.List(ListBox1.ListIndex, 2)
End Sub

 

[Honnore]

Hi Rick...
First of all, thanks for this fast reply.
I changed my code to the following and it wotks partially:
UserForm2.TextBox1.Value = UserForm1.ListBox1.List(i, 9)

I say partially because if I want to add in the TextBox a value that is above
the 9th column of the selected item in the ListBox, it doesn't work anymore.
Strange.

If you or someone else has an idea it would be more than welcome

Try this DblClick event code instead of what you posted...

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
UserForm2.TextBox1 = ListBox1.List(ListBox1.ListIndex, 2)
End Sub

Hi there dear reader...
Usually I find all responses to my Excel issues in already existing

[quoted text clipped - 18 lines]

Thank you in advance for your help
Honnore

 

[Rick Rothstein]

What do you mean by "a value that is above the 9th column"? And what is the
"i" variable for? The code I posted was meant to replace all of your code
(notice I posted the event header and the End Sub statements with my single
line of code)... it was not meant to be merged with the code you posted.
Because you are using the DblClick event, the selected row will be the row
that the user double clicks on... the ListIndex that I used as an argument
for the List property returns that row number directly (where the first row
is numbered 0)... no loop is necessary to find it. Again, the code you will
actually need will depend on the first question I asked you above (and on
what "above" means in it).

--
Rick (MVP - Excel)

Hi Rick...
First of all, thanks for this fast reply.
I changed my code to the following and it wotks partially:
UserForm2.TextBox1.Value = UserForm1.ListBox1.List(i, 9)

I say partially because if I want to add in the TextBox a value that is
above
the 9th column of the selected item in the ListBox, it doesn't work
anymore.
Strange.

If you or someone else has an idea it would be more than welcome

Try this DblClick event code instead of what you posted...

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
UserForm2.TextBox1 = ListBox1.List(ListBox1.ListIndex, 2)
End Sub

Hi there dear reader...
Usually I find all responses to my Excel issues in already existing

[quoted text clipped - 18 lines]

Thank you in advance for your help
Honnore

 

[Honnore via OfficeKB.com]

Let me put it in another way...
I have a ListBox which I populate with the additem "method". The ListBox has
14 column.
I would like to extract the value of each column of the selected item into
TextBoxes that are in an other Useform...
The here below code you gave me works fine until the 9th column:

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
UserForm2.TextBox1 = ListBox1.List(ListBox1.ListIndex, 9)
End Sub

When willing to retreive the value of the 10th column and above it doesn't
work anymore. In clear words, the here below code doesn't work

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
UserForm2.TextBox1 = ListBox1.List(ListBox1.ListIndex, 10)
End Sub

What do you mean by "a value that is above the 9th column"? And what is the
"i" variable for? The code I posted was meant to replace all of your code
(notice I posted the event header and the End Sub statements with my single
line of code)... it was not meant to be merged with the code you posted.
Because you are using the DblClick event, the selected row will be the row
that the user double clicks on... the ListIndex that I used as an argument
for the List property returns that row number directly (where the first row
is numbered 0)... no loop is necessary to find it. Again, the code you will
actually need will depend on the first question I asked you above (and on
what "above" means in it).

Hi Rick...
First of all, thanks for this fast reply.

[quoted text clipped - 20 lines]

 

[Rick Rothstein]

I'm not sure what to tell you... the code works fine on my tests... I can
pull values past column 9 with no trouble using that line of code. Perhaps
you have a counter locked at a maximum of 9 somewhere in your code.

--
Rick (MVP - Excel)

Let me put it in another way...
I have a ListBox which I populate with the additem "method". The ListBox
has
14 column.
I would like to extract the value of each column of the selected item into
TextBoxes that are in an other Useform...
The here below code you gave me works fine until the 9th column:

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
UserForm2.TextBox1 = ListBox1.List(ListBox1.ListIndex, 9)
End Sub

When willing to retreive the value of the 10th column and above it doesn't
work anymore. In clear words, the here below code doesn't work

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
UserForm2.TextBox1 = ListBox1.List(ListBox1.ListIndex, 10)
End Sub

What do you mean by "a value that is above the 9th column"? And what is
the
"i" variable for? The code I posted was meant to replace all of your code
(notice I posted the event header and the End Sub statements with my
single
line of code)... it was not meant to be merged with the code you posted.
Because you are using the DblClick event, the selected row will be the row
that the user double clicks on... the ListIndex that I used as an argument
for the List property returns that row number directly (where the first
row
is numbered 0)... no loop is necessary to find it. Again, the code you
will
actually need will depend on the first question I asked you above (and on
what "above" means in it).

Hi Rick...
First of all, thanks for this fast reply.

[quoted text clipped - 20 lines]

Thank you in advance for your help
Honnore

 

[Honnore via OfficeKB.com]

It probably works for you because you have linked your listbox to a row
source. In that case it works.

I'm not sure what to tell you... the code works fine on my tests... I can
pull values past column 9 with no trouble using that line of code. Perhaps
you have a counter locked at a maximum of 9 somewhere in your code.

Let me put it in another way...
I have a ListBox which I populate with the additem "method". The ListBox

[quoted text clipped - 35 lines]