run a macro in excel formula

[rabbi]

Hi all

Is there a way i can get an excel formula to execute a macro if the
conditions are true. eg.

excel formula:

=if(c1>10,(macro),"-"


(macro) to be an autofilter function which I have written. I am
looking at automating the macro from the formula rather than someone
having to press the assigned button. If there is a simple way, any
help would be appreciated

cheers

Rabbi

 

[Norman Jones]

Hi Rabbi,

A formula cannot execute a macro but macros can run in reponse to certain
events such as a change in the value of a specified cell.

For Exanple:

'=============>>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

Set rng = Me.Range("C1")

If Not Intersect(rng, Target) Is Nothing Then
If rng.Value > 10 Then
'Do something, e.g.:
MsgBox rng.Value
End If
End If

End Sub
'<<=============

This is worksheet event code and should be pasted into the worksheets's code
module (not a standard module and not the workbook's ThisWorkbook module):

*******************************************
Right-click the worksheet's tab

Select 'View Code' from the menu and paste the code.

Alt-F11 to return to Excel.
*******************************************

For examples of Event procedures see David Mcritchie's Events page at:

http://www.mvps.org/dmcritchie/excel/event.htm

For an in depth discussion of Event procedures see Chip Pearson's Events
page at:

http://www.cpearson.com/excel/events.htm