I've now tried that, and it give me "Error 2023" - whatever that means!

However, in a flash of inspiration I found something that works: using
the Parent property.

Thanks for your help.
Darren

Bill Renaud wrote:
I tried it inside a class module of mine that processes firewall log data,
and it seems to work, but you might be correct in that it might not work
for forms.

Try:

Dim varCaller as Variant

varCaller = Application.Caller

Set a breakpoint right after the final line above, then inspect varCaller
in the Locals window to see what you get.

Visual Basic Help for the Caller Property only lists a few types of
callers, and says an error is returned for any other type not listed, but
maybe this information is incomplete. (I am running Excel 2000.)

I know it will work for a macro called from a CommandBar button, as
varCaller returns a 2 element array, one element gives the button number,
and the other element lists the name of the CommandBar.

<<I found something that works: using the Parent property.

That would make perfect (more logical) sense!

--
Regards,
Bill Renaud

Application.Caller can be used only in Function procedure that was called
from a worksheet cell (in which case it returns a Range reference to the
cell(s) from which the function was called), or in a procedure called via
the OnAction property of a Shape object (in which case it is a String value
containing the name of the Shape). In any other context, Application.Caller
is an Error-type Variant containing a #REF error value.


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


"Darren Hill" wrote in message
...
I've now tried that, and it give me "Error 2023" - whatever that means!

However, in a flash of inspiration I found something that works: using the
Parent property.

Thanks for your help.
Darren

Bill Renaud wrote:
I tried it inside a class module of mine that processes firewall log
data,
and it seems to work, but you might be correct in that it might not work
for forms.

Try:

Dim varCaller as Variant

varCaller = Application.Caller

Set a breakpoint right after the final line above, then inspect varCaller
in the Locals window to see what you get.

Visual Basic Help for the Caller Property only lists a few types of
callers, and says an error is returned for any other type not listed, but
maybe this information is incomplete. (I am running Excel 2000.)

I know it will work for a macro called from a CommandBar button, as
varCaller returns a 2 element array, one element gives the button number,
and the other element lists the name of the CommandBar.

In Excel 2000, for shapes that are controls on a CommandBar,
Application.Caller returns a 2 element array: element 1 is the control that
called the routine, and element 2 is the name of the CommandBar.

For example, if the first button on a CommandBar named "CommandBar Name"
calls the following routine:

Public Sub Test()
Dim varCaller as Variant

varCaller = Application.Caller
End Sub

....then varCaller will be the following:
varCaller(1) = 1
varCaller(2) = "CommandBar Name"

This is true, even if Sub Test is a method inside a class module.

Has this changed in later versions?
--
Regards,
Bill Renaud

Thanks for the explanation, Chip.
By the way, I love your website - that's an astounding collection of
Excel support materials.

Darren

Chip Pearson wrote:
Application.Caller can be used only in Function procedure that was
called from a worksheet cell (in which case it returns a Range reference
to the cell(s) from which the function was called), or in a procedure
called via the OnAction property of a Shape object (in which case it is
a String value containing the name of the Shape). In any other context,
Application.Caller is an Error-type Variant containing a #REF error value.