Reference column names instead of numbers

[Mindlesh]

How can I adapt these macros to reference the corresponding column names in Table4?

Private Sub ToggleButton1_Click()
    [COLOR=#ff0000]' Reference a column named "Time"[/COLOR]
    ActiveSheet.Columns(3).Hidden = Not (ToggleButton1.Value)
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
[COLOR=#ff0000]' Reference a column named "ET"[/COLOR]
If Target.Cells.Count = 1 And Target.Column = 4 Then
    ActiveSheet.Sort.SortFields.Clear
    [COLOR=#ff0000]' Reference the second cell in a column named "NT"[/COLOR]
    ActiveSheet.Sort.SortFields.Add Key:=Range("B2") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveSheet.Sort.SortFields.Add Key:=Range("A2") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    [COLOR=#ff0000]' Reference Table4[/COLOR]
    With ActiveSheet.Sort
        .SetRange Range("A:M")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End If
End Sub

 

[Macropod]

You can use column letters instead of numbers (e.g. "C" instead of 3), but it's now clear what you mean by 'Reference a column named "Time"'.
If "Time" is a named range you might use:
ActiveSheet.Range("Time").Columns.Hidden or ActiveSheet.Range("Time").EntireColumn.Hidden
If "Time" is a cell value in a column, your code will need to work out what column that is (e.g. via Find).

 

[Mindlesh]

"Time" is the name of a column in a range that is formatted as a table named "Table4"; the sheet has multiple tables. I want to adapt the macros to refer to specific table and column names.

 

[Macropod]

So did you try using ActiveSheet.Range("Time")?

 

[Mindlesh]

I get run-time error '1004': application-defined or object-defined error.

 

[MARK858]

Try

Range("Table4[[#All],[Time]]").Select

or

Range("Table4[[#All],[Time ]]").Select

 

[Mindlesh]

This works:

ActiveSheet.Range("Table4[[#All],[Time]]").Columns.Hidden = Not (ToggleButton1.Value)

What other changes should I make to use this concept across both macros?

 

[MARK858]

One way of referencing the 2nd cell in the column is

Range("Table4[[#All],[NT]]").Cells(2).Select

or another...

Range("Table4[NT]").Cells(1).Select

One way of referencing a whole table is

ActiveSheet.ListObjects("Table4").Range.Select

 

[Mindlesh]

Am I on the right track?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim tb As ListObject
    Set tb = ActiveSheet.ListObjects("Table4").Range.Select
    If Target.Cells.Count = 1 And Target.Column = Range("NT") Then
        tb.Sort.SortFields.Clear
        tb.Sort.SortFields.Add Key:=Range("NT").Cells(2).Select _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        tb.Sort.SortFields.Add Key:=Range("Se").Cells(2).Select _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With tb.Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End If
End Sub

 

[MARK858]

Try...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim tb As ListObject
    Set tb = ActiveSheet.ListObjects("Table4")
    If Target.Cells.Count = 1 And Target.Column = tb.ListColumns("NT").DataBodyRange.Column Then
        tb.Sort.SortFields.Clear
        tb.Sort.SortFields.Add Key:=tb.ListColumns("NT").DataBodyRange.Cells(1) _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        tb.Sort.SortFields.Add Key:=tb.ListColumns("Se").DataBodyRange.Cells(1) _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With tb.Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End If
End Sub