Run Macro from if(function)

[Dutchie]

Can anybody tell me if its possible to run a macro from an if statement as in
=if(weekday(A1)=5,Executemacro,"")

Or else how to program the result of a macro to a cell when a certain condition exists?

I already have a macro that takes data from an active cell with a running number:

Range("B5").Select
Selection.Copy
Range("E26").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Now I need to run this Macro when its friday (example)

Can't find it in the VB help files.
Appreciate a pro's eye on this.

 

[Legacy 11779]

On 2002-11-07 09:22, Dutchie wrote:
Thanks PhilR and Hi Pear,

I just read your posts. Haven't had a chance to get on it. Is Pear suggesting something else here?
I have not found any posts on this subject before.

Dutchie

I'm suggesting that you can't call a macro from a function(including UDF's) if the macro has to change the Excel environment.
If the macro is merely showing a message box (for example) then that's OK, but that could be done in the function itself without the need for a separate macro.

Precedents and dependents of a cell need to be known to calculate the worksheet.
Since Excel doesn't know what's happening in your functions, they are prohibited from changing other cells.

Chip Pearson explains as follows :-

"The reason you cannot have a formula like =IF(A1>10,MyMacro)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."

This topic has arisen a number of times on this board. Here are a couple of examples :-

http://www.mrexcel.com/board/viewtopic.php?topic=21879&forum=2

http://www.mrexcel.com/board/viewtopic.php?mode=viewtopic&topic=10747&forum=2&start=10
This message was edited by Pear on 2002-11-07 21:11

 

[WillShakes]

Hi,

I have been trying to do something and have a bit of cod ethat seems to half contradict this last statement, and half-support it!

What I'm Trying to do
On a quotation spreadsheet, I have a particluar checkbox selection, lest calls CB1. When CB1 is checked, I want another option to appear, but when CB1 is not selected, that new option we'll call cb2 should disappear.

After a bunch of failed starts I finally came up with this, which only half works:

Function InsertPly(myCheck As Boolean)
    If myCheck = True Then
        ActiveSheet.Shapes("Check Box 37").Visible = True
        Range("A19").NumberFormat = "General"
        InsertPly = True
    Else
        ActiveSheet.Shapes("Check Box 37").Visible = False
        Range("A19").NumberFormat = ";;;"
        InsertPly = False
    End If

The checkbox disappears and reappears as required, but the cell I have placed the word "Plywood" in won't hide and unhide! I assume this is because you can't "change the excel environment" from a function as mentioned, but then why can I make the checkbox appear and diappear?

Hmmm... I am wondering if I can create a label next to the checkbox and hid/unhide that?

But anyway, is there a way to hide/unhide cells based on selecting a checkbox? I bet I am missing something so basic I'll be embarrassed when I find out!

Will

 

[Mikejyt33]

I have a similar example and question =if(a5,runmacro1,runmacro2). I am still new to VBA and do not understand the answers given or how to insert the macro. I am more of a English language guy not a vba smiley face. So if you could start from the beginning that will be most appreciated. I am more of a English language guy not a vba smiley face.

 

[Mikejyt33]

=if(a5=b,macro1,macro2)