excel and macros

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Excel and Macros

Excel and Macros

resolvedResolved · Medium Priority · Version 2003

Nick has attended:
Excel Advanced course

Excel and Macros

When using an IF function in excel can you initiate a Macro in the true or false statement?

Edited on Fri 9 May 2008, 23:49

RE: Excel and Macros

Hi Nick, Thank you for your post, welcome to the forum, the answer to your question is; NO, The reason you cannot have a formula like =IF(A1>10,MyMacro,0)in a worksheet cell is because Excel must keep track of which cells are dependents and precedents of which other cells. It must do this in order to calculate the worksheet in the proper order. VBA code which could change worksheet cells could irreversible confuse the order of calculations. Therefore, Excel forbids code called from a worksheet cell from changing anything in the Excel environment. A FUNCTION procedure can only return a value to Excel, nothing more.

However, you could use the worksheet's Change event to do something like this.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Application.Intersect(Target, Range("A1")) Is Nothing Then
If Target.Value > 10 Then MsgBox "Put Your Code Here"
End If
End If
End Sub

regards Pete


 

Excel tip:

Display Formulas Instead of Results in Excel 2010

By pressing Ctrl ~ once, Excel will display formulas rather than the results of the formulas. Press it again, and the results will appear again.

A much quicker and simpler way of displaying your formulas!

View all Excel hints and tips


Server loaded in 0.08 secs.