One method using a sheet change event.

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

On Tue, 25 Sep 2007 10:32:03 -0700, Frank Erb
wrote:

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?

Frank Erb

"Gord Dibben" wrote:

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


Gord Dibben MS Excel MVP


On Tue, 14 Aug 2007 09:04:00 -0700, LW_Greeney
wrote:

mIS there anyway we can activate a macro from within a formular for exaple if
cell A1 ="" then run Macro?