Insert =now() macro on clicking a cell?

[Eville]

Hi again,

Still struggling but i try 2 describe what im looking for;
When an action is done, it needs verified with a time
This i want to do by clicking a certain cell (different cells in different collums & rows)
So im wondering if its possible by simply clicking a cell to enter the current time (that doesnt change anymore) of eg B13, G7:G19 B23, G19:27 etc.
So far i tried to record a macro (called time) and let the macro run with:


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("G7:G19")) Is Nothing Then 'Enter the cell range that the user is going to clikc in here
Call Time 'the name of your macro
End If
End Sub

But this doesnt work >< Any suggestions?

Ev

PS: Time macro looks like:


Sub Time()
' Time Macro
Selection.NumberFormat = "h:mm;@"
ActiveCell.FormulaR1C1 = "=NOW()"
Range("B13:B14").Select
End Sub

[GTO]

Hi Ev,

When an action is done, you want it verified with a timestamp, and you want to do this by clicking (or double-clicking according to the event you listed). I am not sure if I understand what it is you want to do. What action are we looking for, where we then want to timestamp?

Or, are you saying that if for instance, the first time the user double-clicks in the range "G7:G19" then B13 gets timestamped?

If that's it, try:


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("G7:G19")) Is Nothing And ActiveSheet.Range("B13") = "" Then
With ActiveSheet.Range("B13")
.NumberFormat = "h:mm;@"
.Value = Time
End With
End If
End Sub

If that's way off base of what you're looking to do, please re-state the question.

Almost forgot... when you are including code, use the green/white VBA button at the top of the message window. Paste the code between the tags.:)

Hope this helps,

Mark

[Eville]

Hi Ev,

When an action is done, you want it verified with a timestamp, and you want to do this by clicking (or double-clicking according to the event you listed). I am not sure if I understand what it is you want to do. What action are we looking for, where we then want to timestamp?

Or, are you saying that if for instance, the first time the user double-clicks in the range "G7:G19" then B13 gets timestamped?

If that's it, try:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("G7:G19")) Is Nothing _
And ActiveSheet.Range("B13") = "" Then
With ActiveSheet.Range("B13")
.NumberFormat = "h:mm;@"
.Value = Time
End With
End If
End Sub

If that's way off base of what you're looking to do, please re-state the question.

Ok i try 2 explain it again. The action i ment was an action you do in real life (eg execute a check). This irl action (the check) needs 2 be registered in my excel sheet. In excel i want it to work something like this:
Normally when u click on a cell in excel, the cell gets selected. When double click the cursor starts blinking.
Now im looking for a code when i click on a cell, the cell gets selected. When i double click the cell, the current time gets inserted.
This code needs 2 be applied for the cells B13, G9, G10, G11, G12, G13,G14, G15, G16, G17 (so B13 and G9:G17) and many more cells in the sheet but once i get a clue on the code i can add the other cells myself i think :P



Almost forgot... when you are including code, use the green/white VBA button at the top of the message window. Paste the code between the tags.:)

Hope this helps,

Mark
Oops im sorry, thx for pointing that out. i never had a clue how 2 do post it in a decent way, now i know :D

Hope my question makes more sence now :D
Thx in advance!

Ev

[Bob Phillips]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Const WS_RANGE As String = "B13,G9:G17" '<== change to suit
On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.NumberFormat = "h:mm;@"
.Value = Now
Cancel = True
End With
End If
ws_exit:Application.EnableEvents = True
End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[Eville]

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

Hello,
tbh i really dont have a clue how u figure out these kind of codes cause they make no sence to me what so ever. I cant even read & understand what each line or command does but im a happy bunny again so thank you very much for helping me out with the code. It works perfect!! :bow:

A happy Ev :rotlaugh: