Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Selected row in a List

Status
Not open for further replies.

NicolaasH

Technical User
Sep 11, 2007
38
Hi,

I would like to ask the following:

How do I detect in which row of a list (ListObject) a selected cell is?

Purpose:
I would like to enable the user to select a cell or row in a list and then they have the option of 2 buttons:
Duplicate Row - the row gets copied and added at the bottom of the list
Delete Row - the row is deleted

The duplication and deleting is not a problem, however I do not know how I can detect which row number in the list has been selected/activated.

Thanks!
Nick
 
The ListObject has the ListRows property that returns a ListRows collection. If the row of your cell is in that collection, you can use the Index property to find the index number in the List.

_________________
Bob Rashkin
 
Bob, thanks, but could you help me out a little more? I'm not sure how to extract the index number yet, I guess that was actually my question. I now have the following code:

Code:
Public Sub DuplicateRow()
Dim ListWorksheet As Worksheet, objList1 As ListObject
Dim SelectedRow As Integer

Set ListWorksheet = ThisWorkbook.Worksheets("List")
Set objList1 = PipelineWorksheet.ListObjects(1)

On Error GoTo WrongSelection
If Not objList1.Active Then GoTo WrongSelection

'determine selected row
'part that does not work yet

SelectedRow = objList1.ListRows.Index

'rest of code
'...
'...

AddRow CopyRow:=SelectedRow, Duplication:=True

Exit Sub
WrongSelection:
MsgBox Prompt:="Duplication Failed", Buttons:=vbCritical, Title:="Duplication Error"

End Sub

...so what shall I put after 'part that does not work yet'.
Thanks!
 
If the user will first select the cell, why not just use
[tab]Activecell.entirerow
or
[tab]Activecell.entirecolumn
?



[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.
 

Yes, I had something like this before,
Code:
ActiveCell.EntireRow.Cells(1, 1).Row
however this returns the row in the sheet, while I am looking for the row in the List, which is a different index different.
 
I still don't see the problem.

Let's say the user presses the duplicate button. Assuming that your data in the list is contiguous, then try this:
Code:
ActiveCell.EntireRow.Copy _
    Destination:=ActiveCell.End(xlDown).Offset(1).EntireRow


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.
 
Unfortunately it is a problem, because it is not merely copying and pasting. I need the ListRow reference to find a couple of other parameters (this happens in the 'rest of code bit, and the procedure AddRow that is called).

To keep the code neat and not run into problems when I move the list, I do not want to define the listrow by something like listrow = absolute reference - 6

 
I have found a solution, be it not a very aesthetic one. I have first determined the absolute row reference of the top of the list and than subtract this from the absolute reference of the selected cell.

Code:
ListPosition = objList1.Range(1, 1).Row
AbsoluteRow = ActiveCell.EntireRow.Cells(1, 1).Row
SelectedRow = AbsoluteRow - ListPosition

if anyone knows a better way of referencing the row index, please let me know.
 
Hi

May be following code help you

Sub check()
Dim objList As ListObject
Dim sh As Worksheet
Dim rng As Range
' i have the list object in sheet1
Set sh = ThisWorkbook.Sheets("Sheet1")
With sh
If .ListObjects(1).Active Then
Set rng = Intersect(ActiveCell, .ListObjects(1).Range)
Debug.Print rng.Row
Debug.Print rng.Column
End If
End With
End Sub

stefen
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top