call Module from MS Excel Object - Probably very easy VB questions

[Kevin]

Hi
I created a Module called "Test" ie sub Test( ). It is a very simple macro
that sorts a few tables in a single sheet

I added the same code to a Workbook open function ie Private Sub
Workbook_Open( ) which works fine

However I want it so that I update the code in one place ie sub Test ( ), I
simply point to this from whereever I want - this way I only update once and
don't need to ensure the code is always the same.

Background is I want to force the sort in many instances eg manually, when I
open the workbook, when I activate a new sheet etc

thanks in advance

 

[Gary''s Student]

First create a public sub in a standard module:

Public Sub hithere()
MsgBox ("hi there")
End Sub

Then from workbook or worksheet event code:

Private Sub Worksheet_Change(ByVal Target As Range)
Call hithere
End Sub

 

[Kevin]

I get a Compile error: Expected variable or procedure, not module

any idea what i have done wrong?

 

[Chip Pearson]

The problem is likely that your code module (which contains the procedure)
has the same name as a procedure. Change the name of the module or the name
of the procedure.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Kevin]

spot on - thanks
--
Kevin

The problem is likely that your code module (which contains the procedure)
has the same name as a procedure. Change the name of the module or the name
of the procedure.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)