Sprietzsche
06-09-2016, 03:01 AM
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 :)
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 :)