Excel 2002, Win XP
I have a sheet macro:
Private Sub Worksheet_Activate()
Call UpdateSummary
End Sub

The UpdateSummary macro looks like this:
Private Sub UpdateSummary()
'Stuff
End Sub

Upon activation of the sheet, I get an error message on the "Call
UpdateSummary" line that it is not defined. If I remove the word "Private"
before the Sub UpdateSummary() all works fine. I wanted to ensure that the
UpdateSummary macro was not displayed in the Tools - Macro - Macros list, so
I put the "Private".
Obviously there is something here that I don't know. What is it?
Thanks for your help. Otto

Otto,

These two modules are in different code modules, one in a sheet code module,
the other in (I presume) a normal code module. You cannot invoke Private
procedures across different modules.

If you include an 'Option Private Module' in the code module, those
procedures will not show in the macro list, but will still be available to
other modules in that project. The one thing you won't be able to do is to
invoke them from another project (workbook).

--

HTH

Bob Phillips

"Otto Moehrbach" wrote in message
...
Excel 2002, Win XP
I have a sheet macro:
Private Sub Worksheet_Activate()
Call UpdateSummary
End Sub

The UpdateSummary macro looks like this:
Private Sub UpdateSummary()
'Stuff
End Sub

Upon activation of the sheet, I get an error message on the "Call
UpdateSummary" line that it is not defined. If I remove the word
"Private"
before the Sub UpdateSummary() all works fine. I wanted to ensure that
the
UpdateSummary macro was not displayed in the Tools - Macro - Macros list,
so
I put the "Private".
Obviously there is something here that I don't know. What is it?
Thanks for your help. Otto

Put it in the same sheet module and you won't get the message.

"Otto Moehrbach" wrote in message
...
Excel 2002, Win XP
I have a sheet macro:
Private Sub Worksheet_Activate()
Call UpdateSummary
End Sub

The UpdateSummary macro looks like this:
Private Sub UpdateSummary()
'Stuff
End Sub

Upon activation of the sheet, I get an error message on the "Call
UpdateSummary" line that it is not defined. If I remove the word
"Private"
before the Sub UpdateSummary() all works fine. I wanted to ensure that
the
UpdateSummary macro was not displayed in the Tools - Macro - Macros list,
so
I put the "Private".
Obviously there is something here that I don't know. What is it?
Thanks for your help. Otto

Bob
Thanks for your help and thanks for taking the time to explain it to me.
Otto
"Bob Phillips" wrote in message
...
Otto,

These two modules are in different code modules, one in a sheet code
module,
the other in (I presume) a normal code module. You cannot invoke Private
procedures across different modules.

If you include an 'Option Private Module' in the code module, those
procedures will not show in the macro list, but will still be available to
other modules in that project. The one thing you won't be able to do is to
invoke them from another project (workbook).

--

HTH

Bob Phillips

"Otto Moehrbach" wrote in message
...
Excel 2002, Win XP
I have a sheet macro:
Private Sub Worksheet_Activate()
Call UpdateSummary
End Sub

The UpdateSummary macro looks like this:
Private Sub UpdateSummary()
'Stuff
End Sub

Upon activation of the sheet, I get an error message on the "Call
UpdateSummary" line that it is not defined. If I remove the word
"Private"
before the Sub UpdateSummary() all works fine. I wanted to ensure that
the
UpdateSummary macro was not displayed in the Tools - Macro - Macros
list,
so
I put the "Private".
Obviously there is something here that I don't know. What is it?
Thanks for your help. Otto

Don
Thanks for your help but how can I put a sheet macro and a regular macro
in the same module? I can put all the code in the sheet macro and that
would do it. Thanks again. Otto
"Don Guillett" wrote in message
...
Put it in the same sheet module and you won't get the message.

"Otto Moehrbach" wrote in message
...
Excel 2002, Win XP
I have a sheet macro:
Private Sub Worksheet_Activate()
Call UpdateSummary
End Sub

The UpdateSummary macro looks like this:
Private Sub UpdateSummary()
'Stuff
End Sub

Upon activation of the sheet, I get an error message on the "Call
UpdateSummary" line that it is not defined. If I remove the word
"Private"
before the Sub UpdateSummary() all works fine. I wanted to ensure that
the
UpdateSummary macro was not displayed in the Tools - Macro - Macros
list,
so
I put the "Private".
Obviously there is something here that I don't know. What is it?
Thanks for your help. Otto

Otto,

From the VBA help on "Sub Statement"

Private Optional. Indicates that the Sub procedure is accessible only to
other procedures in the module where it is declared.

Put the sub in the sheet module and use

Private Sub Worksheet_Activate()
Macro1
End Sub

Private Sub Macro1()
*code*
End Sub

--
sb
"Otto Moehrbach" wrote in message
...
Excel 2002, Win XP
I have a sheet macro:
Private Sub Worksheet_Activate()
Call UpdateSummary
End Sub

The UpdateSummary macro looks like this:
Private Sub UpdateSummary()
'Stuff
End Sub

Upon activation of the sheet, I get an error message on the "Call
UpdateSummary" line that it is not defined. If I remove the word
"Private"
before the Sub UpdateSummary() all works fine. I wanted to ensure that
the
UpdateSummary macro was not displayed in the Tools - Macro - Macros list,
so
I put the "Private".
Obviously there is something here that I don't know. What is it?
Thanks for your help. Otto

Or another way is to leave it private, but run it using Run, which can call
private procedures and functions:

Application.Run "UpdateSummary" 'The Application part is optional


"Otto Moehrbach" wrote in message
...
Excel 2002, Win XP
I have a sheet macro:
Private Sub Worksheet_Activate()
Call UpdateSummary
End Sub

The UpdateSummary macro looks like this:
Private Sub UpdateSummary()
'Stuff
End Sub

Upon activation of the sheet, I get an error message on the "Call
UpdateSummary" line that it is not defined. If I remove the word
"Private"
before the Sub UpdateSummary() all works fine. I wanted to ensure that
the
UpdateSummary macro was not displayed in the Tools - Macro - Macros list,
so
I put the "Private".
Obviously there is something here that I don't know. What is it?
Thanks for your help. Otto

Hi Otto

Lots of good explanations are provided.
Great thing with macros on standard module are that they can be called from
everywhere. But they can also be started from the Tools Macro menu, which
sometimes is a very bad idea. A typical set of macros in my apps are "unlock
everything" and "re-lock everything", used by lots of procedures. I don't
want any of my users to ever access those.

So put Private in the name and they disappear from the menu. But then they
can't be called from other modules either. Which is a huge problem. My
solution to this -I never use Private: Put an optional parameter to it
that's not used for anything:

Sub RunStuff(Optional RightNow As Boolean)
MsgBox "Hello world"
End Sub

-and it disappears completely from the user, but still it can be called from
all modules:

Sub SomewhereElse()
Call RunStuff
End Sub

--
HTH. Best wishes Harald
Excel MVP
Followup to newsgroup only please

"Otto Moehrbach" skrev i melding
...
Excel 2002, Win XP
I have a sheet macro:
Private Sub Worksheet_Activate()
Call UpdateSummary
End Sub

The UpdateSummary macro looks like this:
Private Sub UpdateSummary()
'Stuff
End Sub

Upon activation of the sheet, I get an error message on the "Call
UpdateSummary" line that it is not defined. If I remove the word
"Private"
before the Sub UpdateSummary() all works fine. I wanted to ensure that
the
UpdateSummary macro was not displayed in the Tools - Macro - Macros list,
so
I put the "Private".
Obviously there is something here that I don't know. What is it?
Thanks for your help. Otto

That's a good point to remember. Thanks. Otto
"Tim Zych" wrote in message
...
Just to note, there might be issues if code is moved behind the worksheet.

Unqualified range references in sheet code always reference the sheet that
holds the code, whereas in a module, they reference the active sheet. It
depends on what your code does, to determine if putting it behind a sheet
is
the best choice. As a matter of personal preference, I tend to allow some
sheet level event code behind the sheet, but not much more. Hard to say
how
it would affect your macro because we don't know what it does. Since your
original goal was to simply hide the macro visibility from the Tools menu,
you may be safest simply by adding Option Private Module at the top of the
module and making the sub public, like Bob suggested (although I like Run
for some things too).

Best regards,
Tim Zych


"Otto Moehrbach" wrote in message
...
Steve
You taught me something I didn't know. I didn't know I could put a
regular (non-event) macro in a sheet module. Thanks. Otto
"steve" wrote in message
...
Otto,

From the VBA help on "Sub Statement"

Private Optional. Indicates that the Sub procedure is accessible only
to
other procedures in the module where it is declared.

Put the sub in the sheet module and use

Private Sub Worksheet_Activate()
Macro1
End Sub

Private Sub Macro1()
*code*
End Sub

--
sb
"Otto Moehrbach" wrote in message
...
Excel 2002, Win XP
I have a sheet macro:
Private Sub Worksheet_Activate()
Call UpdateSummary
End Sub

The UpdateSummary macro looks like this:
Private Sub UpdateSummary()
'Stuff
End Sub

Upon activation of the sheet, I get an error message on the "Call
UpdateSummary" line that it is not defined. If I remove the word
"Private"
before the Sub UpdateSummary() all works fine. I wanted to ensure
that
the
UpdateSummary macro was not displayed in the Tools - Macro - Macros
list,
so
I put the "Private".
Obviously there is something here that I don't know. What is
it?
Thanks for your help. Otto