A Conditional Macro

  • Thread starter Thread starter Dudley Wright
  • Start date Start date
D

Dudley Wright

I'm working on a project for my VP, and I was wondering if there is a
way to reference a macro button within an "if" statement.
If a cell, for example cell C18, returns a certain value, I would like
macros to run automatically.... is it possible to start/run a macro on
a conditional basis?
Thanks for any suggestions
Dudley
 
Hi Dudley
one way: use the worksheet change event. Put the following in your
worksheet module:
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("C18")) Is Nothing Then Exit Sub
With Target
if .value = "some value" then
'run your code
end if
End With
End Sub
 
You can call a macro directly from a cell

=IF(A1>0,myFunc(),"")

but it cannot set cell attributes, it can only open files, show MsgBox,
etc., or return a value.

Other than that, you would use worksheet change event code to trap a range
and its value and run some code thereafter.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Good point that the user *might* be able to use a function instead
of a macro. But that limitation to only returning a value is what
distinguishes Functions from Macros. Chip Pearson has a web
page on the difference between a Macro and a Function.

Macros and Functions (Macros as Opposed to Functions)
http://www.cpearson.com/excel/differen.htm

The Change Event macro will only pick up if you change the
value in a cell, not if a formula (calculation) changes the value.
If the formula is dependent on directly to other cells that the
user changes, you could trigger off of them.

If you have to run your Event macro based on a calculation
event happening you could be wasting a lot of machine cycles.

Worksheet Events and Workbook Events
http://www.mvps.org/dmcritchie/excel/event.htm
 
Back
Top