Macro to add date when you double click a cell in a specific range

[Raceman]

Hi, I'm trying to figure out how to write a macro that will add date to a cell if you double click it. But this feature should only apply to a range of cells $C4:$E1000. The date should not change the next day when the file is opened

 

[pgc01]

Hi

Add to the worksheet module:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

' only for range C4:E1000
If Intersect(Target, Range("C4:E1000")) Is Nothing Then Exit Sub

Application.EnableEvents = False
Target.Value = Date
Application.EnableEvents = True
Cancel = True
End Sub

 

[Raceman]

perfect thank you very much!

 

[pgc01]

You're welcome. Thanks for the feedback.

 

[Rick Rothstein]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

' only for range C4:E1000
If Intersect(Target, Range("C4:E1000")) Is Nothing Then Exit Sub

[COLOR=#FF0000][B]Application.EnableEvents = False[/B][/COLOR]
Target.Value = Date
[COLOR=#FF0000][B]Application.EnableEvents = True[/B][/COLOR]
Cancel = True
End Sub

I am not sure I see any benefit in disabling events within the BeforeDoubleClick procedure. If there was code in the Change event that applied to the subject range, why wouldn't you want it to run?

 

[pgc01]

I am not sure I see any benefit in disabling events within the BeforeDoubleClick procedure. If there was code in the Change event that applied to the subject range, why wouldn't you want it to run?

Well, I don't know the OP's complete solution and so I don't know if the Change event should or should not run after the double-click.

I think that in the code I posted it's very clear that no other event is fired when the date is written in the cell. I think that's a good thing. It's very easy to delete the enable/disable statements if they are not needed. Even a less experienced user reads the code and understands it. Those statements shout it loud and clear.

The other alternative would be not to have included them in the code. In my opinion that's not so good. A less experienced user might not know that events can fire other events and it might not be so easy to understand what was happening if things didn't work as expected.

It's just my opinion, of course. I understand there would be also valid arguments against including the statements.

 

[Rick Rothstein]

Well, I don't know the OP's complete solution and so I don't know if the Change event should or should not run after the double-click.

I think that in the code I posted it's very clear that no other event is fired when the date is written in the cell. I think that's a good thing. It's very easy to delete the enable/disable statements if they are not needed. Even a less experienced user reads the code and understands it. Those statements shout it loud and clear.

The other alternative would be not to have included them in the code. In my opinion that's not so good. A less experienced user might not know that events can fire other events and it might not be so easy to understand what was happening if things didn't work as expected.

It's just my opinion, of course. I understand there would be also valid arguments against including the statements.

Fair enough.