Run Macros from an IF statement within a formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

Maybe you could tie into one of excel's worksheet events--either
worksheet_Change or worksheet_calculate.
 
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
 
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
 
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
 
Back
Top