Run Macro from if(function)

Dutchie

New Member
Joined
Nov 6, 2002
Messages
4
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.
 
L

Legacy 11779

Guest
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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

WillShakes

New Member
Joined
Jan 30, 2010
Messages
1
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:

Code:
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

New Member
Joined
Jan 24, 2018
Messages
2
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,903
Messages
5,638,899
Members
417,058
Latest member
BRYCEPIETROWIAK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top