List Selection to First Empty Row
 

I have inherited a worksheet which has a macro that places the selectio
from a listbox into a cell on a worksheet. That becomes the range tha
is placed on the main worksheet when the user clicks ok. The proble
is that range is then placed wherever the cursor is in the mai
worksheet. I need to alter or add to the vba language to make th
selection go to the first empty row in column A so that the user canno
place the entry anywhere but in column A. Could anyone please look a
this for me? Thank you in advance for any suggestions

Private Sub CommandButton1_Click()
'if the listindex of listbox equals -1 ... nothing selected
If ListBox1.ListIndex = -1 Then
MsgBox "No item selected", vbExclamation
Exit Sub
End If
Range("SelectionLink") = ListBox1.ListIndex + 1
Selection.Cells(1) = Worksheets("Products").Range("D1")
Selection.Cells(1).Offset(0, 1) = Worksheets("Products").Range("E1")
Unload Me
End Su

--
Message posted from http://www.ExcelForum.com

List Selection to First Empty Row
 

"depuyus " wrote in message
...
I have inherited a worksheet which has a macro that places the selection
from a listbox into a cell on a worksheet. That becomes the range that
is placed on the main worksheet when the user clicks ok. The problem
is that range is then placed wherever the cursor is in the main
worksheet. I need to alter or add to the vba language to make the
selection go to the first empty row in column A so that the user cannot
place the entry anywhere but in column A. Could anyone please look at
this for me? Thank you in advance for any suggestions

Private Sub CommandButton1_Click()
'if the listindex of listbox equals -1 ... nothing selected
If ListBox1.ListIndex = -1 Then
MsgBox "No item selected", vbExclamation
Exit Sub
End If
Range("SelectionLink") = ListBox1.ListIndex + 1
Selection.Cells(1) = Worksheets("Products").Range("D1")
Selection.Cells(1).Offset(0, 1) = Worksheets("Products").Range("E1")
Unload Me
End Sub


---
Message posted from http://www.ExcelForum.com/




Find the part in the code where it pastes the selection and before it add
the following code

Sheets("sheetname").Select
Worksheets("sheetname").Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select


Dave

List Selection to First Empty Row
 

I'll try to help, but I still have a bit of trouble to understan
without any sample data:

Eg What Value is contained in the Listbox?

Then Range("Selectedlink") seems to be a named range.....

Well anyways. This might help you:
When I have a contineous list of values, and want to find the nex
available row, I use this:


Code
-------------------

Range ("A1").select '(top left corner of my list)
ActiveCell.Offset(ActiveCell.CurrentRegion.Rows.Co unt, 0).Select

-------------------


--
Message posted from http://www.ExcelForum.com