I have a function in the Sheet1, and I also call this function from
Sheet2. There are many msgbox in this function which I only want them
to execute when called by Sheet1. That is, when the function is called
by Sheet2, I don't want any msgbox shown. The problem is how I can get
to know which sheet is calling. Thanks!

I would think that msgbox's in UDF's would soon get irritating to the user, but
this kind of thing works ok for me:

Option Explicit
Function myFunc()

If LCase(Application.Caller.Parent.Name) = LCase("sheet1") Then
MsgBox "hi there"
End If

myFunc = "bye there!"
End Function




"lvcha.gouqizi" wrote:

I have a function in the Sheet1, and I also call this function from
Sheet2. There are many msgbox in this function which I only want them
to execute when called by Sheet1. That is, when the function is called
by Sheet2, I don't want any msgbox shown. The problem is how I can get
to know which sheet is calling. Thanks!

--

Dave Peterson

Thanks. It works. But when I call from a module, it doesn't work again.
Any idea?

maybe...

Option Explicit
Function myFunc()

If TypeName(Application.Caller) = "Range" Then
If LCase(Application.Caller.Parent.Name) = LCase("sheet1") Then
MsgBox "hi there"
End If
End If

myFunc = "bye there!"
End Function



"lvcha.gouqizi" wrote:

Thanks. It works. But when I call from a module, it doesn't work again.
Any idea?

--

Dave Peterson

Thank you very much! It works now.