none
ActiveSheet.ListObjects("Table1").Row.Value RRS feed

  • Question

  • Super simple, but escapes me.

    How do I get the table row value of the active cell in excel using VBA?

    I'd like to set a cell value inside a table dependant on a userform checkbox... Something like

    If UserForm1.CheckBox1.Value = True Then
    	Sheet1.ListObjects("Table1[Column1]").ListRows.Value = "Yes"
    

    Though that doesn't work. So I was hoping I could do something like

    If UserForm1.TextBox1.Value = True then
    	Sheet1.ListObjects("Table1[Column1]").DataBodyRange(cRow, 3).Value = "Yes"

    all help appreciated

    Cheers

    ... 

    Thursday, January 7, 2016 4:33 PM

Answers

  • sRow = Selection.Row
    aRow = ActiveSheet.ListObjects("Table1").HeaderRowRange.Row
    cRow = sRow - aRow
    'or
    cRow = Selection.Row - ActiveSheet.ListObjects("Table1").HeaderRowRange.Row

    • Marked as answer by _Brad_C_ Wednesday, February 10, 2016 3:18 PM
    Wednesday, February 10, 2016 3:18 PM

All replies

  • >>>Though that doesn't work. So I was hoping I could do something like

    According to your description, you could refer to below code:

    Dim tbl As ListObject
    Set tbl = ActiveSheet.ListObjects("Table1")
    tbl.ListRows(1).Range.Cells(1, 2).Value = "Yes"

    then get this result:

    Friday, January 8, 2016 4:29 AM
  • Thanks David,

    That helps somewhat.  I'm struggling to determine what row in the table I'm referring too however.  Is there a way to get the table row value?

    On a related note, how do I correctly code:

    Union(ActiveSheet.ListObjects("Table1[Column1]"), ActiveSheet.ListObjects("Table1[Column4]")).EntireColumn.Hidden = False

    All help much appreciated.

    Cheers

    Brad

    Thursday, January 14, 2016 1:36 PM
  • >>>That helps somewhat.  I'm struggling to determine what row in the table I'm referring too however.  Is there a way to get the table row value?

    According to your description, if I don't misunderstand, you could use ListObject's Range and DataBodyRange property to achieve your requirement, you could refer to below code:

    ActiveSheet.ListObjects("Table2").Range.Copy Range("C1")

    or

    ActiveSheet.ListObjects("Table2").DataBodyRange.Copy Range("D1")

    • Marked as answer by David_JunFeng Thursday, January 21, 2016 1:45 AM
    • Unmarked as answer by _Brad_C_ Saturday, February 6, 2016 7:13 AM
    Saturday, January 16, 2016 5:27 AM
  • Thanks David,

    My problem really revolves around determining the table row value of the currently selected cell. 

    At the moment Range("C5") is selected. I can easily determine the current row in relation to the sheet using ActiveCell.Row (5).  How do I do this in relation to the table (4) and the table.datarange (3)? 

    I'd like to use VBA to update the field on the selected row in the [Active] column with code similar to:

        If Range("Table1[[#This Row],[Active]]").Value = "No" Then
            Range("Table1[[#This Row],[Active]]").Value = "Yes"
        End If

    Doing it this way will provide greater flexibility.  [Active] may not always be column(4).

    Cheers

    Brad 

    Saturday, February 6, 2016 7:13 AM
  • BTW for anyone interested in the Union syntax issue:

    Union(Range("Table1[Column 1]"), _
            Range("Table1[Column 2]"), _
            Range("Table1[Column 6]"), _
            Range("Table1[Column 9]"), _
            Range("Table1[Column 10]")).EntireColumn.Hidden = False

    • Marked as answer by _Brad_C_ Saturday, February 6, 2016 7:19 AM
    • Unmarked as answer by _Brad_C_ Saturday, February 6, 2016 7:19 AM
    Saturday, February 6, 2016 7:19 AM
  • sRow = Selection.Row
    aRow = ActiveSheet.ListObjects("Table1").HeaderRowRange.Row
    cRow = sRow - aRow
    'or
    cRow = Selection.Row - ActiveSheet.ListObjects("Table1").HeaderRowRange.Row

    • Marked as answer by _Brad_C_ Wednesday, February 10, 2016 3:18 PM
    Wednesday, February 10, 2016 3:18 PM