Macro with in a formula

  • Thread starter Thread starter Howard
  • Start date Start date
H

Howard

Can a macro be run from with in a formula? If so how is it
started from with in the formula? If a macro can not be
run from within the formula can it be automaticaly
triggered an entry in a cell?
Thank you
Howard
 
Howard,

A function, including a UDF, can be run from within a formula, but these
functions can't amend the properties of any cells, and are really only
useful to return a value.

If you want to update other cells, you need VBA event code.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
use the sheet's CHANGE event. This is fired when a new
value is entered into a cell. It is not fired when a
cell's value changes as a result of a formula.
Right click the sheet tab and select view code.
In the object dropdown( upper left corner) where you see
(General), select Worksheet.
The code automatically frames in the SelectionChange
method as this is the default. You can ignore this.
From the declarations dropdown(upper right) that now
shows 'SelectionChange' choose 'Change' and the following
code will be added:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub


Target is the cell whose value changed
You can now add code to call a procedure ( or macro by
another name), for example..

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case True
Case Not (IsNumeric(Target.Value))
'do nothing
Case Target.Value <= 0
MsgBox " enter a number greater than 0"
Case Target.Value > 5
MsgBox " enter a number less than 6"
Case Else
End Select

End Sub

HTH
Patrick MOlloy
Microsoft Excel MVP
 
Back
Top