Worksheet object code

[mcgew]

I've written some code in ms excel objects, for a worksheet, and want to call code for another objects worksheet at the end. I've tried

Sub Sheet1()
(code)
Call Sheet2

End Sub
................
Sub Sheet2()
(code)
End Sub

but when i try to run it it says there's an expected function or variable. it works when i use modules but for this code i need it in the worksheet.
any suggestions?

[Damon Ostrander]

Hi mcgew,

I don't believe the scope rules in Excel permit calling a procedure that is within a worksheet's event code module from any other module. The way around this is to put the common code you want to apparently use from both modules in a standard macro module (as you mentioned). So your example would look like:

Sub Sheet1()
(code)
Call CommonCode

End Sub
................
Sub Sheet2()
Call CommonCode
End Sub

----------------
'inside standard macro module

Sub CommonCode()
.
.
End Sub


So Sub Sheet1 can be called from events within sheet1's event code module, Sub Sheet 2 can be called from events within sheet2's event code module, and the two can share the common code.

[mcgew]

Thanks for your help.

Grace :D