OzGrid

Insert time/date with click on cell but never update

Requirement:

The user has a series of cells (>200) that the user would like the time or date to be entered into with a click or double click.

The user is using this code as of now but has two questions. How can the user easily define my range of cells and how can the user keep the cells from updating if they are double clicked in the future?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("A1:B10")) Is Nothing Then
        Cancel = True
        Target.Formula = Now()
    End If
End Sub

As for defining the cell ranges the user knows the starting cell and it will be every 12 rows will be included in the range.

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/149071-insert-time-date-with-click-on-cell-but-never-update

Solution:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("A1:B10")) Is Nothing Then Exit Sub
If Target.Font.Bold = True Then Exit Sub

  Target.Formula = Now()
  Target.Font.Bold = True
  Cancel = True
End Sub

Obtained from the OzGrid Help Forum.

Solution provided by Carim.

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

See also:

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

Gallery