Tomorrows Date - double click

[trevolly]

Hi all

Can I ask for some help on this vba code? I'm trying to set up a code for when I double click on a cell it enters tomorrows date (not tomorrow, the 16/09 but whatever the next days date is) This code brings up an error ...... "Compile Error: Ambiguous name detected: Worksheet_BeforeDoubleClick

Cheers all
T

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Date stamp
If Not Intersect(Target, Range("D20")) Is Nothing Then
Cancel = True
Target.Value = Today() + 1
End If
End Sub

 

[Joe4]

code brings up an error ...... "Compile Error: Ambiguous name detected: Worksheet_BeforeDoubleClick

It sound like you maybe have two blocs of code with the same name "Worksheet_BeforeDoubleClick". That is not allowed. You cannot have two procedures with the same name inside the same module. You will need to combine them into one.

 

[hrayani]

try

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Date stamp
If Not Intersect(Target, Range("D20")) Is Nothing Then
Cancel = True
Target.Value = Target.Value + 1
End If
End Sub

 

[trevolly]

It sound like you maybe have two blocs of code with the same name "Worksheet_BeforeDoubleClick". That is not allowed. You cannot have two procedures with the same name inside the same module. You will need to combine them into one.

Ahhh Ok. Yeh Ive got a vba code on the worksheet for inputting the time - 1 hour (zulu) when I double click certain cells. I was hoping to apply double click with the date + 1 day for different cells on the same worksheet. Oh well back to the drawing board

Thanks

 

[Joe4]

Ahhh Ok. Yeh Ive got a vba code on the worksheet for inputting the time - 1 hour (zulu) when I double click certain cells. I was hoping to apply double click with the date + 1 day for different cells on the same worksheet. Oh well back to the drawing board

If they are applying to different cells, then there should be no problem with combining them in one procedure!

Note the block of code that you currently have:

If Not Intersect(Target, Range("D20")) Is Nothing Then
    Cancel = True
    Target.Value = Today() + 1
End If

This tells it to ONLY do this if cell D20 is double-clicked.

So you can easily add another block of code under this that affects some other cell.
You can actually add as many as you want!

So you should be able to comvine your two procedures into one.
If you have any trouble with that, please post the other procedure code.

 

[trevolly]

Ah that sounds promising... yeh its different cells that the two codes would apply to, Atm Ive got this running on the worksheet...and it works

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Date stamp
If Not Intersect(Target, Range("F2,E23,K20,K23,M20,M23")) Is Nothing Then
Cancel = True
Target.Value = Now() - (1 / 24)
End If
End Sub

I did add a line of code but it failed. Any help would be greatly appreciated
Thanks for your time
T

 

[Joe4]

Quite literally, all you have to do is take the body of the code of the one, and drop it into the other after what is already in there, i.e.

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

    'Date stamp
    If Not Intersect(Target, Range("F2,E23,K20,K23,M20,M23")) Is Nothing Then
        Cancel = True
        Target.Value = Now() - (1 / 24)
    End If
    
    If Not Intersect(Target, Range("D20")) Is Nothing Then
        Cancel = True
        Target.Value = Today() + 1
    End If
    
End Sub

 

[trevolly]

Quite literally, all you have to do is take the body of the code of the one, and drop it into the other after what is already in there, i.e.

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

    'Date stamp
    If Not Intersect(Target, Range("F2,E23,K20,K23,M20,M23")) Is Nothing Then
        Cancel = True
        Target.Value = Now() - (1 / 24)
    End If
   
    If Not Intersect(Target, Range("D20")) Is Nothing Then
        Cancel = True
        Target.Value = Today() + 1
    End If
   
End Sub

Lol I tried that but I didnt leave the first "End If" command in!! Thank you

 

[Joe4]

You are welcome!

 

[trevolly]

Hey @Joe4 - tried this code today and when I double clicked in one of the cells with with the +1 date (ie: E21) I got a Compile error and the first line of the code went yellow

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Date stamp with zulu
If Not Intersect(Target, Range("H24,K24,N24,D28:D52")) Is Nothing Then
Cancel = True
Target.Value = Now() - (1 / 24)
End If

If Not Intersect(Target, Range("E21,J21,N21,E24")) Is Nothing Then
Cancel = True
Target.Value = Today() + 1
End If

End Sub