Double click to increment dates in a table

[Mindlesh]

Using the Worksheet_BeforeDoubleClick event, and referencing column "Date" in "Table1", how can I increment dates by one month?

 

[My Aswer Is This]

It's easier when you tell us what column.
Like column "B" or column "D"

How are we to know what column is Column "Date"
Try this assuming we are dealing with column "D"

If not modify script:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("[COLOR=#ff0000]D:D[/COLOR]")) Is Nothing Then
Cancel = True
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Target.Value = DateAdd("m", 1, Target.Value)
End If
End Sub

 

[Mindlesh]

Thank you; but I was hoping to be able to reference the name of the column to save having to update the script when I change the table.

 

[My Aswer Is This]

Are you saying the Column has been Named "Date".

How are we to know what column is column "Date"?
You have to give me some way of knowing what column is column "Date"

Thank you; but I was hoping to be able to reference the name of the column to save having to update the script when I change the table.

 

[Norie]

Do you want to only increment the date that has been clicked in the column or the entire column?

If it's the former try this.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim tbl As ListObject
Dim rngDateCol As Range

    Set tbl = Me.ListObjects("Table1")
    
    Set rngDateCol = tbl.ListColumns("Date").DataBodyRange
    
    If Not Intersect(Target, rngDateCol) Is Nothing Then
    
        Cancel = True
        
        Target.Value = DateAdd("m", 1, Target.Value)

    End If
    
End Sub

 

[Mindlesh]

Thank you.