Check if activecell is in a column with date in the header in any table

[Sprietzsche]

Iim trying to get a macro running using Excel 2010 to check if the active cell is in certain columns of any table, but i have trouble checking wheter the cel is in a certain column.


My file consists of multible tables containing multiple columns. Some of these columns have “date” as part of their header name. (like “date send”, “date received”, “date rejected”, …). I want to start a macro when activecell is in one of these colums in any of these tables. (the macro should put todays date in the cell when you doubleclick it)


I have found something online for checking whether the activecell is in a table:

Function IsActiveCellInTable() As Boolean
    Dim rngActivecell
    Set rngActivecell = ActiveCell
    Debug.Print IsActiveCellInTable
    On Error Resume Next
    rngActivecell = (rngActivecell.ListObject.Name <> "")
      On Error GoTo 0
    IsActiveCellInTable = rngActivecell

The names of the columns are identical in all tables, and there are only a few categories of columns with date. So case-statements look like an option to me.

However, in the long run i want the option to add columns to those tables, and if those columns contain the word “date” they also should execute the macro.
Also it should be possible to delete columns in a table: so the name of the headers will allways be the same, but the columnumber in the table might change

Any suggestions for checking if the header contains the word "date"?
Thanks in advance

[p45cal]

try:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Set yyy = Target.ListObject
If Not yyy Is Nothing Then
  Cancel = True
  If InStr(UCase(yyy.ListColumns(Target.Column - yyy.Range.Column + 1).Name), "DATE") > 0 Then Target.Value = Date
End If
End Sub

in the sheet concerned's code-module.
It's a little more complex than it could be so as to cater for the possibility that the user could have chosen to hide the table's header row in the TableTools, Design tab of the ribbon.

[Sprietzsche]

Looks like it's not doing anything... If i play with >0 (change it to >=0, >-1, ...) it sets the date in all columns of any table, but >0 does nothing?

But this is exactly what I need... thank you in advance

And thanks for your tip for accessing my own thread

[p45cal]

I tested it here - can you attach a file exhibiting this behaviour?

edit post posting: Are these real Excel tables, or just areas of the sheet with headers? (I assumed they were as you mentioned ListObjects in your code which are Tables proper.)

[Sprietzsche]

While preparing a new file for you I got it to work the way you posted p45cal
And yes, real tables (dynamic ranges were needed)

i think i just messed up with copy/pasting the code in a testfile


I know what I did wrong: i forgot using caps while translating "DATE"