Can VB code just run without being Called ??

[StanSz]

I've got some VB code that does what I want it to do. I've used this code to replace formulas in the Worksheet since I want to turn Calculation to Manual.

I've placed the Code in the appropriate Worksheet module and it works fine if I Call that routine.

But, how do I get the VB code to just run when the workbook is open, and do what I want it to do when Cells change on that worksheet, without having to specifically Call it's routine ??

If I need to continually Call the routine then why not leave the formulas in the Excel cells, and turn the Calculation back to Automatic ??

Thanks

 

[shyrath]

Needs to be pasted into the worksheet module ( right click on the sheet tab name and view code )

Private Sub Worksheet_Change(ByVal Target As Range)

Call MyMacro

End Sub

 

[StanSz]

Thank you,

So, if I understand you correctly, I rename my code routine as MyMacro (as per your example), and then I paste your Private Sub Worksheet_Change(ByVal Target As Range)
code into the Worksheet module as well, yes ??

Will I need to define the Target as Range ??

 

[shyrath]

Hey,

No need to specify a target if you want it to run on any change in any cell.
No need to change the name of your macro.

just put the name of the sub instead of mymacro

so if your sub is

sub NewFormulas ()

then the call would be
Call NewFormulas

 

[StanSz]

Great !

I have 5 different worksheets, each with exactly the same cells and exactly the same VB code pertaining to those cells (the worksheets are identical except for different data).

Can I put the Call NewFormulas sub in each of the five worksheet modules and then put the actual New Formulas sub in a different module ??

I don't need to have the actual NewFormulas sub in EACH of the five worksheets modules, do I ??

 

[shyrath]

Yes, you can place that in each worksheet.

or you can call the macro for each sheet from only the first. Should work both.

 

[lenze]

You can also place it once in the ThisWorkBook module as

Private Sub WorkBook_Change(ByVal Target as Range)

From here will wil run on a change on any sheet.

lenze

 

[StanSz]

Thanks to all