Q. How do I code a column to insert current date, when I double-click?
 

Hi,
I have a spreadsheet, where I'd like the date column to enter the
current date, when I double-click on that particular cell. It will
always be a cell, in the column labeled "Date".
Thanks,
Jim

Q. How do I code a column to insert current date, when I double-click?
 

Jim/George,

In the codemodule of the thisworkbook object, insert the code below.

Un-comment the line
'Cancel = True
if you don't want to stay in edit mode after the double-click.

Change the format to your desired style from the mmm dd, yyyy, and you're
done.

HTH,
Bernie
MS Excel MVP

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _
ByVal Target As Range, Cancel As Boolean)
If IsError(Application.Match("Date", _
Target.EntireColumn, False)) Then Exit Sub
Application.EnableEvents = False
Target.Value = Date
Target.NumberFormat = "mmm dd, yyyy"
Target.EntireColumn.AutoFit
Application.EnableEvents = True
'Cancel = True

End Sub


"George" wrote in message
...
Hi,
I have a spreadsheet, where I'd like the date column to enter the
current date, when I double-click on that particular cell. It will
always be a cell, in the column labeled "Date".
Thanks,
Jim

Q. How do I code a column to insert current date, when I double-click?
 

Try these:
Assume column A is the date column

' This one works on double click. The select portion is to exit the cell
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Target.Column = 1 Then
Target = Date
Target.Offset(0, 1).Select
End If
End Sub

' This one works on a single click, but will also fire with any selection
change.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then
Target = Date
End If
End Sub


--
steveB

Remove "AYN" from email to respond
"George" wrote in message
...
Hi,
I have a spreadsheet, where I'd like the date column to enter the
current date, when I double-click on that particular cell. It will
always be a cell, in the column labeled "Date".
Thanks,
Jim

Q. How do I code a column to insert current date, when I double-click?
 

Why not use Cancel in the double click event as stated by Bernie? Probably
better to use the features provided.

--
Regards,
Tom Ogilvy

"STEVE BELL" wrote in message
news:Rsipe.12888$yS2.107@trnddc07...
Try these:
Assume column A is the date column

' This one works on double click. The select portion is to exit the cell
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Target.Column = 1 Then
Target = Date
Target.Offset(0, 1).Select
End If
End Sub

' This one works on a single click, but will also fire with any selection
change.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then
Target = Date
End If
End Sub


--
steveB

Remove "AYN" from email to respond
"George" wrote in message
...
Hi,
I have a spreadsheet, where I'd like the date column to enter the
current date, when I double-click on that particular cell. It will
always be a cell, in the column labeled "Date".
Thanks,
Jim

Q. How do I code a column to insert current date, when I double-click?
 

Tom,

How right you are!

--
steveB

Remove "AYN" from email to respond
"Tom Ogilvy" wrote in message
...
Why not use Cancel in the double click event as stated by Bernie?
Probably
better to use the features provided.

--
Regards,
Tom Ogilvy

"STEVE BELL" wrote in message
news:Rsipe.12888$yS2.107@trnddc07...
Try these:
Assume column A is the date column

' This one works on double click. The select portion is to exit the cell
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Target.Column = 1 Then
Target = Date
Target.Offset(0, 1).Select
End If
End Sub

' This one works on a single click, but will also fire with any selection
change.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then
Target = Date
End If
End Sub


--
steveB

Remove "AYN" from email to respond
"George" wrote in message
...
Hi,
I have a spreadsheet, where I'd like the date column to enter the
current date, when I double-click on that particular cell. It will
always be a cell, in the column labeled "Date".
Thanks,
Jim