calling procedures (newbie here )

[Piyush]

i dont know how to call my procedure..

in sheet one..i have Sub Testfile1() and sheet2 i have Sub Testfile2().

Then in the Private Sub Workbook_Open()

i put

Private Sub Workbook_Open()

TestFile1
TestFile2
If Weekday(Now) = vbFriday Then
ThisWorkbook.SaveCopyAs "H:\600 series PB free" & ThisWorkbook.Name
End If


End Sub

It keeps giving me an error that i havent defined sub or procedure. I went to tools >> references but it was dim..so i couldnt select it. I tried to use the CALL Testfile1 and Testfile 2. Same problem. Whats the problem? Please advise

[Piyush]

...

im new here. sorry

[anilsolipuram]

You should create a new module, in visual basic editor right click "microsoft excel object"-> insert ->module


you have to paste both procedures TestFile1,TestFile2 in the module

[Piyush]

i dont get it..whats the point in modules vs putting the code into the Sheets??

How do u ever use code from sheets?? u have to keep copying and pasting?? Thats wierd..

[anilsolipuram]

code in sheet1,sheet2,sheet3 ... is for controlling the data in sheet1,sheet2 ,sheet3... respectively not the whole workbook


The code written in sheet's serves as controlling element for data in that sheet only.

Every sheet has predefined function like worksheet_activate, deactivate,change,selectionchange....... ' it is easy understand what each function serves for.

But for controlling the whole workbook you have to type your code in the module, don't use sheets editor.

[Piyush]

ok that makes sense...

but my testfile1() controls whats in sheets 1..why do i have to copy into a module?? its not controlling the workbook. Just the worksheet.

If testfile1 and testfile2 control the data in its respectable worksheets, why does it have to be in a module? its not necessarily being used for the entire workbook. or is it? I know it works and ur method is right...just doesnt make sense why? if im not making sense...lemme know

[anilsolipuram]

The code in the sheets is used solely for

1) to invoke predefined functions i.e
worksheet_activate ' invoked after the sheet is activated
worksheet_change ' invoked after any cell is change and ...................so on


and sometimes you can use your own function in sheets, but that funtion can be called from predefined in the same sheet.

In otherwise only way to invoke the userdefined function in a sheet is to call from predefined function in the same sheet.

So it always good to use module to execute any data from any sheet.

[Piyush]

we cant use predefined functions in modules??

i mean...shouldnt be a problem??

[anilsolipuram]

by predefined function I mean predefined procedures like worksheet_activate ......


In the previous post the predefined function means predefined procedure, not function

[Piyush]

so ur tellign me that what u put in sheet1 for example is pretty much "automated" depeneding on the function. so if u use worksheet_activate. The moment the workbook opens up it does its activation on that sheet automatically and does not really care about anything else occuring in any other portion of the workbook?? Ok if thats true..that makes sense completely. Thank you

[anilsolipuram]

Whenever the worksheet is activated it will automatically call worksheet_activate procedure.

It is actually a empty procedure , but you can write your code in it.

sub worksheet_activate()

'your code

end sub