VBA code to see if an entire row has been selected.

[dpaton05]

I need some code to see if an entire row has been selected within a range and also I need code to see if an entire row has been selected within a table, tblCosting.

Thanks,
Dave

 

[igold]

Does this work for your table.

Sub test()


    Dim tbl As Object
    Dim i As Long
    
    Set tbl = ActiveSheet.ListObjects("tblCosting")
    For i = 1 To tbl.ListRows.Count
        If Selection.Address = tbl.ListRows(i).Range.Address Then
            MsgBox "Row: " & i & " is selected."
            Exit Sub
        End If
    Next
    MsgBox "No table rows selected"
    
End Sub

You could do the same thing for a range. Instead of tbl.Listrows(i), you could use

range.rows(i).Address

 

[dpaton05]

What would be the code to see if a row has not been selected?

 

[dpaton05]

Sorry, I didn't read the rest of the code. I will try it now.

 

[dpaton05]

Whether I have a row selected or not, I get the message box saying No table rows selected.

 

[Michael M]

Maybe something like this......I guess the obvious question is WHY do you need to do this ???

Sub MM1()
If Intersect(ActiveCell, ActiveSheet.ListObjects("tblCosting").DataBodyRange) Is Nothing Then
      MsgBox "activecell is NOT within the table"
   Else
      MsgBox "active cell is within the table"
End If
End Sub

 

[JoeMo]

Whether I have a row selected or not, I get the message box saying No table rows selected.

igold's code doesn't comprehend the possibility that multiple, perhaps non-contiguous, table rows are selected - perhaps that's the source of your unexpected results.
This code will catch multiple row selections within the table including non-contiguous:

Sub test()
    Dim tbl As Object, Ar As Range
    Dim i As Long, j As Long, k As Long
    Set tbl = ActiveSheet.ListObjects("tblCosting")
    If TypeName(Selection) = "Range" Then
        If Not Intersect(Selection, tbl.Range) Is Nothing Then
            For i = 1 To Selection.Areas.Count
                For j = 1 To Selection.Areas(i).Rows.Count
                    For k = 1 To tbl.ListRows.Count
                        If Selection.Areas(i).Rows(j).Address = tbl.ListRows(k).Range.Address Then
                            MsgBox "Row: " & k & " of the table is selected."
                        End If
                    Next k
                Next j
            Next i
        Else
            MsgBox "No table rows selected"
        End If
    Else
        MsgBox "Current selection is not a range object"
    End If
End Sub

 

[dpaton05]

Maybe something like this......I guess the obvious question is WHY do you need to do this ???

Sub MM1()
If Intersect(ActiveCell, ActiveSheet.ListObjects("tblCosting").DataBodyRange) Is Nothing Then
      MsgBox "activecell is NOT within the table"
   Else
      MsgBox "active cell is within the table"
End If
End Sub

How do I change the code to fire the alert if an entire row in the table is not selected?

 

[dpaton05]

igold's code doesn't comprehend the possibility that multiple, perhaps non-contiguous, table rows are selected - perhaps that's the source of your unexpected results.
This code will catch multiple row selections within the table including non-contiguous:

Sub test()
    Dim tbl As Object, Ar As Range
    Dim i As Long, j As Long, k As Long
    Set tbl = ActiveSheet.ListObjects("tblCosting")
    If TypeName(Selection) = "Range" Then
        If Not Intersect(Selection, tbl.Range) Is Nothing Then
            For i = 1 To Selection.Areas.Count
                For j = 1 To Selection.Areas(i).Rows.Count
                    For k = 1 To tbl.ListRows.Count
                        If Selection.Areas(i).Rows(j).Address = tbl.ListRows(k).Range.Address Then
                            MsgBox "Row: " & k & " of the table is selected."
                        End If
                    Next k
                Next j
            Next i
        Else
            MsgBox "No table rows selected"
        End If
    Else
        MsgBox "Current selection is not a range object"
    End If
End Sub

This code seems to work, thanks. Now for some more testing and I might have to get back to you.

 

[Michael M]

Maybe this....but my question remains.....WHY ???

Sub MM1()
ActiveCell.EntireRow.Select
If Intersect(ActiveCell.EntireRow, ActiveSheet.ListObjects("tblCosting").DataBodyRange) Is Nothing Then
      MsgBox "active row is NOT within the table"
   Else
      MsgBox "active row is within the table"
End If
End Sub