ListBox problem
 

Can anyone tell me why the following code will not place the contents of
column 1 in the active cell, and column 2 in the cell to the right of the
active cell? The ListBox has 7 columns.



Private Sub CommandButton3_Click()

Dim rng

Set rng = Cells(ActiveCell.Row, 1)

rng(1, 0).Value = ListBox1.List(ListBox1.ListIndex, 0)
rng(1, 1).Value = ListBox1.List(ListBox1.ListIndex, 7)

Unload EmployeeList

End Sub

ListBox problem
 

Hi Patrick,

Set rng = Cells(ActiveCell.Row, 1)

sets the rng variable to the column A cell of the active row.

The expression:

rng(1, 0).Value = ListBox1.List(ListBox1.ListIndex, 0)

is equivalent to:

rng.Offset(0,-1).Value = ListBox1.List(ListBox1.ListIndex, 0)

and it it will therefore fail because ot is not possible it is not possible
to reference in a column before column A.

rng(1, 1).Value = ListBox1.List(ListBox1.ListIndex, 7)

If the listbox has 7 columns, the last column will be column 6, as the index
is zero-based index

---
Regards,
Norman


"Patrick Simonds" wrote in message
...
Can anyone tell me why the following code will not place the contents of
column 1 in the active cell, and column 2 in the cell to the right of the
active cell? The ListBox has 7 columns.



Private Sub CommandButton3_Click()

Dim rng

Set rng = Cells(ActiveCell.Row, 1)

rng(1, 0).Value = ListBox1.List(ListBox1.ListIndex, 0)
rng(1, 1).Value = ListBox1.List(ListBox1.ListIndex, 7)

Unload EmployeeList

End Sub

ListBox problem
 

I used OFFSET as part of the range definition worked for me, I notice that
your column references are 0 and 7 suggesting there might be 8 columns?
When you stated there are 7, so you might want to check that out as well.

Private Sub CommandButton3_Click()

Dim rng

Set rng = Cells(ActiveCell.Row, 1)

rng.Offset(0, 0).Value = ListBox1.List(ListBox1.ListIndex, 0)
rng.Offset(0, 1).Value = ListBox1.List(ListBox1.ListIndex, 7)

Unload Me

End Sub

--
Cheers
Nigel



"Patrick Simonds" wrote in message
...
Can anyone tell me why the following code will not place the contents of
column 1 in the active cell, and column 2 in the cell to the right of the
active cell? The ListBox has 7 columns.



Private Sub CommandButton3_Click()

Dim rng

Set rng = Cells(ActiveCell.Row, 1)

rng(1, 0).Value = ListBox1.List(ListBox1.ListIndex, 0)
rng(1, 1).Value = ListBox1.List(ListBox1.ListIndex, 7)

Unload EmployeeList

End Sub

ListBox problem
 

Patrick,

Should your second line be:

rng(1, 1).Value = ListBox1.List(ListBox1.ListIndex, 1)

(substituting "1" for "7")?

hth,
Doug


"Patrick Simonds" wrote in message
...
Can anyone tell me why the following code will not place the contents of
column 1 in the active cell, and column 2 in the cell to the right of the
active cell? The ListBox has 7 columns.



Private Sub CommandButton3_Click()

Dim rng

Set rng = Cells(ActiveCell.Row, 1)

rng(1, 0).Value = ListBox1.List(ListBox1.ListIndex, 0)
rng(1, 1).Value = ListBox1.List(ListBox1.ListIndex, 7)

Unload EmployeeList

End Sub

ListBox problem
 

Thank You your code worked to a point. I need this to work from the active
cell not from column A. So if I am in cell B5 I want the results in cells B5
and B6 or if in cell R20 the results would need to go into cells R20 and R21
ect.



"Nigel" wrote in message
...
I used OFFSET as part of the range definition worked for me, I notice that
your column references are 0 and 7 suggesting there might be 8 columns?
When you stated there are 7, so you might want to check that out as well.

Private Sub CommandButton3_Click()

Dim rng

Set rng = Cells(ActiveCell.Row, 1)

rng.Offset(0, 0).Value = ListBox1.List(ListBox1.ListIndex, 0)
rng.Offset(0, 1).Value = ListBox1.List(ListBox1.ListIndex, 7)

Unload Me

End Sub

--
Cheers
Nigel



"Patrick Simonds" wrote in message
...
Can anyone tell me why the following code will not place the contents of
column 1 in the active cell, and column 2 in the cell to the right of the
active cell? The ListBox has 7 columns.



Private Sub CommandButton3_Click()

Dim rng

Set rng = Cells(ActiveCell.Row, 1)

rng(1, 0).Value = ListBox1.List(ListBox1.ListIndex, 0)
rng(1, 1).Value = ListBox1.List(ListBox1.ListIndex, 7)

Unload EmployeeList

End Sub

ListBox problem
 

Hi Patrick,

Try:

ActiveCell.Value = ListBox1.List(ListBox1.ListIndex, 0)
ActiveCell(2, 1).Value = ListBox1.List(ListBox1.ListIndex, 6)

---
Regards,
Norman



"Patrick Simonds" wrote in message
...
Thank You your code worked to a point. I need this to work from the active
cell not from column A. So if I am in cell B5 I want the results in cells
B5 and B6 or if in cell R20 the results would need to go into cells R20
and R21 ect.



"Nigel" wrote in message
...
I used OFFSET as part of the range definition worked for me, I notice
that
your column references are 0 and 7 suggesting there might be 8 columns?
When you stated there are 7, so you might want to check that out as well.

Private Sub CommandButton3_Click()

Dim rng

Set rng = Cells(ActiveCell.Row, 1)

rng.Offset(0, 0).Value = ListBox1.List(ListBox1.ListIndex, 0)
rng.Offset(0, 1).Value = ListBox1.List(ListBox1.ListIndex, 7)

Unload Me

End Sub

--
Cheers
Nigel

ListBox problem
 

Thanks a lot works great.


"Norman Jones" wrote in message
...
Hi Patrick,

Try:

ActiveCell.Value = ListBox1.List(ListBox1.ListIndex, 0)
ActiveCell(2, 1).Value = ListBox1.List(ListBox1.ListIndex, 6)

---
Regards,
Norman



"Patrick Simonds" wrote in message
...
Thank You your code worked to a point. I need this to work from the
active cell not from column A. So if I am in cell B5 I want the results
in cells B5 and B6 or if in cell R20 the results would need to go into
cells R20 and R21 ect.



"Nigel" wrote in message
...
I used OFFSET as part of the range definition worked for me, I notice
that
your column references are 0 and 7 suggesting there might be 8 columns?
When you stated there are 7, so you might want to check that out as
well.

Private Sub CommandButton3_Click()

Dim rng

Set rng = Cells(ActiveCell.Row, 1)

rng.Offset(0, 0).Value = ListBox1.List(ListBox1.ListIndex, 0)
rng.Offset(0, 1).Value = ListBox1.List(ListBox1.ListIndex, 7)

Unload Me

End Sub

--
Cheers
Nigel