Run Macros from an IF statement within a formula

[Guest]

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

 

[Dave Peterson]

Not from a formula.

Maybe you could tie into one of excel's worksheet events--either
worksheet_Change or worksheet_calculate.

 

[Gord Dibben]

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

 

[Guest]

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

 

[JE McGimpsey]

See

http://www.mcgimpsey.com/excel/timestamp.html

 

[Gord Dibben]

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