insert values of a listbox with double clik in a range

[nicitalia]

Hi there guys,

it's been a long time since I wrote on the forum.

I've got a problem with a UserForm_ListBox. I created a UF with 2 listbox that have 3 columns each. Once selected a row, with a double clik event, I want that the values of the selected row to be put in a precise range of the worksheet, and the in the next free row and so on...

I want that the selected rows to be put in range B320, in the first fre row and so on...

Eg: values of the select row of the listbox1:
column 1 = 05/18/2011 --> this value is entered in B3
column 2 = ford focus --> C3
column 3 = 15.000 $ --> D3


I've found this code, it works but it puts the values of the row of the listbox in the first free row of the worksheets, starting from "A1".

Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

Dim x As Long, p As Long, n As Long
On Error Resume Next

p = ListBox1.ListIndex
If p = "-1" Then Exit Sub
n = cells(rows.count,1).end(xlup).row + 1
For x = 0 To 2
Cells(n, x + 1) = ListBox1.List(p, x)
Next x

End Sub

Any help is realy apprecieted...thank you guys!

Nic

 

[SkipVought]

hi,

Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

Dim x As Long, p As Long, n As Long
On Error Resume Next

p = ListBox1.ListIndex
If p = "-1" Then Exit Sub
n = cells(rows.count,1).end(xlup).row + 1 [b]
if n < 3 then n=3[/b]
For x = 0 To 2
Cells(n, x + [b]2[/b]) = ListBox1.List(p, x)
Next x

End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[nicitalia]

Hi Skip,

firt of all thank you for your hint, but this code in not working: I double clik on a row of the listbox but nothing happens.

How could I upload an example file?

Thak you.

Nic

 

[SkipVought]

I've found this code, it works but it puts the values of the row of the listbox in the first free row of the worksheets, starting from "A1".

I was answering this question, making the code start in row 3 column 2 as in your example...

Eg: values of the select row of the listbox1:
column 1 = 05/18/2011 --> this value is entered in B3
column 2 = ford focus --> C3
column 3 = 15.000 $ --> D3

I am confused!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[nicitalia]

The point is: how to find the first free row of the range B319?

I mean, everutime I double clik a row of a listbox I want the values to be put in the first free row of the range. Everytime I ripeat thi action (double clik on a row) the sub must write the values in the next free row.

May if I send you the file it should be easier for me to explain!

Thaks a lot.

Nic

 

[SkipVought]

sorry. I missed this change in my previous code...

    n = Cells(Rows.Count, [b]2[/b]).End(xlUp).Row + 1

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}