How to Double click a blank cell to enact "ctrl + ;"

[Emredrum]

I would like to be able to double click a cell to insert the date. I know
ctrl + ; will insert it, but is there a way to have the cell read a double
click to do what i want?

 

[Gord Dibben]

Using event code you could do it.

For one worksheet only..................

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Target.Value = Format(Now, "hh:mm:ss")
Cancel = True
End Sub

This is sheet event code.

Right-click the sheet tab and "View Code"

Copy/paste the above into that sheet module.

Alt + q to return to the Excel window.

Any cell you d-click will get a static time entered.

For all worksheets......................

Copy this into Thisworkbook module

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _
ByVal Target As Range, Cancel As Boolean)
Target.Value = Format(Now, "hh:mm:ss")
Cancel = True
End Sub

Right-click on the Excel Icon left of "File" on worksheet menubar and "View
Code" to access Thisworkbook module.

If you go with the second event code, don't use the first code in the sheet.


Gord Dibben MS Excel MVP

 

[ShaneDevenshire]

Hi,

I think you need to try some slightly different code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Target = Format(Now, "m/d/yy")
Cancel = True
End Sub

I believe the previous code would have formatted as time not date.

 

[Rick Rothstein]

I would think you can simplify your assignment to this...

Target.Value = Time

However, I would point out that the OP asked to duplicate Ctrl+; which
inserts the date, not time, into a cell. To do that, he can change your
assignment line to this...

Target.Value = Date

 

[Emredrum]

I would like to be able to double click a cell to insert the date. I know
ctrl + ; will insert it, but is there a way to have the cell read a double
click to do what i want?

That worked perfectly, but of course now i would like to do a little more..

Within this same structure, can i have it so i can assigned a cell range to
reflect a different option when dlciked. IE, Double click B6 to insert date,
then B7 To insert the time, then c6 for date, c7 for time. the cell ranges
can be specified.

So im looking for these double click options also using a cell ref for their
decisions. Yes i am lasy.

Thanks in advance.

 

[Gord Dibben]

Some day I will actually read the description that OP posts.


Gord

 

[Gord Dibben]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
If Not Intersect(Target, Me.Range("B6:B12")) Is Nothing Then
Target.Value = Date
End If
If Not Intersect(Target, Me.Range("C6:C12")) Is Nothing Then
Target.Value = Time
End If
Cancel = True
End Sub


Gord

 

[Rick Rothstein]

Not sure if this would be considered more cryptic or not, but it is a little
more compact...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
If Not Intersect(Target, Me.Range("B6:C12")) Is Nothing Then
Target.Value = Choose(Target.Column - 1, Date, Time)
End If
Cancel = True
End Sub

 

[Gord Dibben]

Neato

Can't say I understand but it works and is more compact.


Gord

 

[Rick Rothstein]

Of course the If..Then statement makes sure the Target cell is in range. The
first argument to the Choose function is a one-based index value into the
remaining arguments... 1 returns the 2nd argument (first argument after the
index argument) and 2 returns the 3rd argument (second argument after the
index argument). Once we know the Target is in range, we just need to see if
the Column is B or C. Since B is the 2nd column and C is the 3rd column,
subtracting 1 from the Target column will yield a 1 or 2 value... this can
be used as the index argument to select the proper following argument in
order to return what is in them (which are function calls to Date or Time).
Choose is in the same family of functions as the Immediate If (IIf)
function; however, where the IIf function selects between two items to
return, the Choose function can select among many more (I didn't see it in
the help files, but the number 30 comes to mind).

 

[Gord Dibben]

I have set this to "Keep" in my Agent reader so's I can refer back to it.

Thanks Rick