Calling Procedures from another worksheet

  • Thread starter Thread starter rob nobel
  • Start date Start date
R

rob nobel

I would like to know how to call a procedure from another worksheet in the
same workbook. I can call a procedure from procedures in the same
worksheet, but not if the procedure I'm calling is in another worksheet.

What I have is the following in one worksheet...
Private Sub LastEntry_Click()
(instructions)
End Sub

And this in another worksheet....
Private Sub ClosePricing_Click()
(instructions)
Call LastEntry_Click
End Sub

Even If I change Private to Public or omit the words Private, it still does
not work and the helps does not make it clear what I'm doing wrong.

Rob
 
Rob,

Application.Run "MyWorkbook.xls!MyMacro"
Application.Run "'My Workbook.xls'!MyMacro"

The apostrophes are necessary if there are spaces in the file name.
 
Hi Earl,
Thanks for this but I don't understand what this will do. How is that going
to use the Call function to call a procedure that is already written within
another worksheet of the same workbook?
Rob
 
Hi again Earl,
I figured it out. It seems that I needed to put the name of the sheet
before the name of the procedure, as follows
Call Sheet1.LastEntry_Click
I hope that's the correct method as it works this time.
Bit annoyed that Helps nor John Walkenbach's Excel 2000 "Power programming"
book don't explain this. Their suggested way is by making procedures
public, etc. but that procedure is not straight forward, nor could I get it
to work.

Rob
 
Hi


Why do you have your procedures on worksheet level at all.

Open the workbook, and press Alt+F11
In VBA Project window, you do see open projects/workbooks. Select the one
labeled as VBAProject (YourWorkbook)
Insert the module (Insert.Module)
Declare the procedure/function. For this select Insert.Procedure, enter the
procedure/function name, select Type (Sub or Function), and Scope (Public or
Private).Press OK - empty procedure or function is created. You have only to
fill in parameters (when needed) and the code between sub/function
declaration and sub/function end statements.

When creating a function, it'll wise have 'Application.Volatile' entered as
first row of code - without it your function isn't recalculated
automatically when source data are changed on worksheet(s). And with
functions, the result is returned with row:
FunctionName=ResultExpression


Arvi Laanemets
 
Thanks Arvi,
This all sounds great and will try to implement your suggestion although I'm
not a wizz on procedures so I may have some more questions later.
Rob
 
Back
Top