Not from within a formula but you can use sheet event code.
Assuming you had a formula in A1 that reads =IF(B1="","",B1)
Private Sub Worksheet_Calculate()
On Error GoTo endit
Application.EnableEvents = False
If Me.Range("A1") = "" Then
macroname
End If
endit:
Application.EnableEvents = True
End Sub
Right-click on the sheet tab and "View Code". Copy/paste to the sheet module.
Note: will not fire if A1 is just blank as in contents cleared.
For that you would need something like this.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo endit
Application.EnableEvents = False
If Me.Range("A1") = "" Then
macroname
End If
endit:
Application.EnableEvents = True
End Sub
If I need to start a new thread just say so. I would like some elaboration on
the macro process described here. I would like to have a worksheet recognize
that a cell has data entered into it and then automatically add a date to the
date column. Could you please help me Gord?
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col A date is entered in column B
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
n = Target.Row
If Excel.Range("A" & n).Value <> "" Then
Excel.Range("B" & n).Value = Format(Now, "dd mmm yyyy hh:mm:ss")
End If
End If
enditall:
Application.EnableEvents = True
End Sub
Adjust columns and date format to suit.
Gord
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.