Enter current date by double click in one column; enter current time by double click in two other columns

[nancylynn718]

Hello,I am very new to VBA and I have basic knowledge of Excel. I have been doing some research and reading up on how to use codes in the VBA but I've run into some issues. I would like to enter the current date by double clicking cells within column A (A3:A1000). I would then like to enter the current time by double clicking cells within columns B (B3:B1000) and F (F3:F10000). I tried to combine the two codes in one but the program would not allow it. As of right now, this is the code I am using for the current time entered on double click:Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)Dim MyRange As RangeDim IntersectRange As RangeSet MyRange = Application.Union( _ ActiveSheet.Range("B3:B1000"), _ ActiveSheet.Range("F3:F1000"))Set IntersectRange = Intersect(Target, MyRange)On Error GoTo SkipItIf IntersectRange Is Nothing ThenExit SubElseActiveSheet.UnprotectApplication.ScreenUpdating = FalseTarget = Format(Now, "ttttt")ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=TrueActiveSheet.EnableSelection = xllockedCellsEnd IfActiveSheet.UnprotectRows("1:3").SelectRange("1:3,A4:E65536").SelectRange("1:3,A4:E65536,G4:IV65536").SelectSelection.Locked = FalseSelection.FormulaHidden = FalseActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _FalseActiveCell.Offset(, 1).SelectSkipIt:Exit SubEnd SubThe second portion of the code is in place because I was having some issues using the filters. Can anyone help me and let me know how to add to this code so I can add the current date by double clicking cells in column A (A3:A1000)? Thank you for your assistance.

 

[Rick Rothstein]

Your code is all but unreadable in the form that you posted it. However, taking a guess at what you want, see if this code does what you want...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Not Intersect(Target, Range("A3:A1000")) Is Nothing Then
    Target.Value = Date
    Cancel = True
  ElseIf Not Intersect(Target, Range("B3:A1000,F3:F1000")) Is Nothing Then
    Target.Value = Time
    Cancel = True
  End If
End Sub

 

[nancylynn718]

Yes, my apologies. I don't know how to insert line breaks in this forum. Perhaps if you tell me how to copy/paste the coding as to include the line breaks, it may read better. The code you provided works just not with the current code I have in place. When I try to put two codes in the VBA, an error message pops up that reads: Ambiguous Name Detected. The issue at hand is joining the two codes. Please advise. Thank you.

 

[Rick Rothstein]

Yes, my apologies. I don't know how to insert line breaks in this forum. Perhaps if you tell me how to copy/paste the coding as to include the line breaks, it may read better. The code you provided works just not with the current code I have in place. When I try to put two codes in the VBA, an error message pops up that reads: Ambiguous Name Detected. The issue at hand is joining the two codes. Please advise. Thank you.

You can only have one event procedure for a given event (double click in this case) per worksheet. What needs to be done is the code I posted has to be integrated with your existing code (mnus anything your existing code does for ranges A3:A1000, B3:B1000 and F3:F1000. If you need help integrating what I posted into what you current have, post your current BeforeDoubleClick event code using code tags as shown below...

[code]

<< Copy/paste your existing code here >>

[/code]

 

[nancylynn718]

Hello!I ended up deleting all coding I originally started with and pasted your coding. It seems to be working perfectly! This is exactly what I needed. Thank you for your help, and thank you for showing me how to input codes into this forum!