macro-vba execute question

[ottasight]

Hi
is there a way for the worksheet to run a macro/vba code on auto-calculation iteration, rather than through an interactive object like a button..............thanks

 

[Smitty]

Sure, you can use the Calculate event.

HTH,

 

[ottasight]

Hi Smitty
how do i use that. do i type "hth" in a cell then name the macro?
i.e. hth, printselectrange.........thanks

 

[HalfAce]

Hi,
Smitty's 'HTH' simply stands for "Hope This Helps." It really has nothing to do with your answer.

What he's referring to is going into your sheet code module (right click the sheet tab & choose View Code), and putting your code into the Worksheet_Calculate event. Then it will get called every time your sheet calculates.

Does that help at all?

 

[ottasight]

thanks HalfAce
but not quite sure what "calculate event" is. if calculate event is in vba code how is it assigned to to run in a cell to get auto-calculated, since the code is run by button or something, what would i write in a cell. i.e. "run,"sub,printwhatever""...........thanks

 

[Rick Rothstein]

Event code is code that gets executed in response to events that take place on your worksheet (or UserForm or ActiveX controls when used within those contexts). Code inside the Calculate event procedure runs whenever the worksheet is recalculated. Anyway, event code is installed in a different location than macro (event code is placed in the sheet module to which it applies as opposed to a standard module used for macros). Also, cell references could be different in event code depending on which event is being used and what the code is doing, so it is kind of hard to give you a general solution without know what your code looks like. Can you post your current macro and provide enough description with it about your data layout and what your code is supposed to be doing with that data?

 

[Smitty]

Here's an example of a Calculate event:

Private Sub Worksheet_Calculate()
    MsgBox "Sheet just calculated"
End Sub

If you put a formula in a cell, like =A1, then change A1 the Message Box should appear.

EDIT: I was on the phone and missed Rick's response. And yes, to confirm what Dan said, "HTH" means "Hope that helps"

 

[ottasight]

Thanks Guys
i think i'm getting it. the worksheet module incorporates the code into the worksheet itself. but still have question. what is " Worksheet_calculate event". is that what the module does or is that something i need to type into the module.......thanks

 

[Smitty]

Yes, you put the event code in a module.

Private Sub Worksheet_Calculate()
    MsgBox "Sheet just calculated"
End Sub

That is an example of a Calculate event, and the code itself goes in the worksheet specific module, not a general module. You don't need to do anything for it to fire, other than a mechanism for your formulas to fire, like changing the value in a dependent cell.

 

[Joe4]

Take a look here for details on the different Event Procedures and how to use them: http://www.cpearson.com/excel/Events.aspx