PDA

View Full Version : [SOLVED] Check if activecell is in a column with date in the header in any table



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 :)

p45cal
06-09-2016, 04:19 AM
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
06-09-2016, 05:01 AM
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
06-09-2016, 05:07 AM
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
06-10-2016, 12:05 AM
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"