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?

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

On Wed, 15 Oct 2008 19:22:00 -0700, Emredrum
wrote:

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?

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.
--
Thanks,
Shane Devenshire


"Emredrum" wrote:

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?

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

--
Rick (MVP - Excel)


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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

On Wed, 15 Oct 2008 19:22:00 -0700, Emredrum
wrote:

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?

"Emredrum" wrote:

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.

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


Gord

On Wed, 15 Oct 2008 21:05:00 -0700, ShaneDevenshire
wrote:

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.

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

On Wed, 15 Oct 2008 22:15:01 -0700, Emredrum
wrote:



"Emredrum" wrote:

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.

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

--
Rick (MVP - Excel)


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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

On Wed, 15 Oct 2008 22:15:01 -0700, Emredrum
wrote:



"Emredrum" wrote:

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.

Neato

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


Gord

On Thu, 16 Oct 2008 14:27:40 -0400, "Rick Rothstein"
wrote:

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

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

--
Rick (MVP - Excel)


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Neato

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


Gord

On Thu, 16 Oct 2008 14:27:40 -0400, "Rick Rothstein"
wrote:

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

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

Thanks Rick




On Thu, 16 Oct 2008 17:08:31 -0400, "Rick Rothstein"
wrote:

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