@matt0020190
"the code updates the timestamp to the current time when the actual cell has not been clicked or changed"
You wrote a function, and used that function in a formula. Formulas are re-evaluated at some times even when a cell is not updated (for example, each time the workbook is opened). So a simple function + formula is not the correct design.
(Perhaps of interest to you, a function that had access to a separate store ("copy") of the values could be a valid design. But of course, that doubles the storage requirements, and you have to prevent anything but the code from updating the separate store. Too complicated.)
NikolinoDE demonstrated how code in an event handler can instead be written to react to a change in a worksheet value (due to user entry, not due to recalculation).
"Not sure on the issue but this is the error"
If you had clicked the Debug button, the VBA Editor would have opened and shown you the line of code that generated the error. (You could then take further actions to understand and possibly correct the problem.) Showing us that line of code and lines that preceded it would help us diagnose your problem.
Of course, you can use online resources to learn about debugging in the VBA Editor, such as:
"I want to specify the columns to check and which cell the timestamp is output"
Those are (or generally should be) specified in the event handler code, not in formulas on the worksheet.
However, VBA is very capable, and you could: a) reserve a cell on the worksheet (let's say K1) to contain identifiers for the range to monitor; b) reserve another cell on the worksheet (let's say K2) to contain an identifier for the column into which to place the timestamp; c) write the event handler code to retrieve and use the values from those cells. Here's how that could look:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim strRangeOfInterest As String
Dim rngRangeOfInterest As Range
Dim strColForTimestamps As String
Dim rngColForTimestamps As Range
'
Dim rngAffectedCellsOI As Range
Dim rngCell As Range
Dim in4CellRow As Long
Dim vntCellContent As Variant
'---- Retrieve information on which cells to monitor and where
' to put timestamps.
strRangeOfInterest = Range("K1").Value
strColForTimestamps = UCase$(Range("K2").Value)
' -- Do some error checking.
On Error Resume Next
Set rngRangeOfInterest = Range(strRangeOfInterest)
On Error GoTo 0
If rngRangeOfInterest Is Nothing Then
Call MsgBox("Invalid range (in K1) of cells to monitor!" _
, vbExclamation + vbOKOnly)
Exit Sub
End If
On Error Resume Next
Set rngColForTimestamps = Range(strColForTimestamps & ":" _
& strColForTimestamps)
On Error GoTo 0
If rngColForTimestamps Is Nothing Then
Call MsgBox("Invalid column (in K2) for timestamps!" _
, vbExclamation + vbOKOnly)
Exit Sub
End If
Set rngColForTimestamps = Nothing 'We don't need it further.
'---- For any cells of interest that were updated...
Set rngAffectedCellsOI = Intersect(Target, rngRangeOfInterest)
If Not (rngAffectedCellsOI Is Nothing) Then
For Each rngCell In rngAffectedCellsOI
vntCellContent = rngCell.Value
in4CellRow = rngCell.Row
'...Update the timestamp for the row.
If vntCellContent = "" Then '[per your code]
Range(strColForTimestamps & in4CellRow).Value = ""
Else
Range(strColForTimestamps & in4CellRow).Value = _
Format(Now, "dd-mm-yyyy hh:mm:ss") '[per your code]
'...or just = Now() if you want to store a date- _
time value and do the formatting in the column
End If
Next rngCell
End If
End Sub
BTW, storing timestamps as text is not a good idea (takes up more storage, cannot readily be sorted or accurately compared to other timestamps).
Side note: I strongly recommend that each of the modules into which you place VBA code (standard modules, workbook module, worksheet modules, userform modules, etc.) should start with an Option Explicit statement. That statement forces you to declare any variables you use, which avoids most bugs due to misspelling of variable names.
