Maybe something like this

Public Sub Test()
MsgBox "hello"
End Sub

Function myTest(rng As Range)
If ActiveCell = Application.Caller Then Call Test
End Function

--

HTH

Bob

"Subodh" wrote in message
...
I have a custom function in Excel
I want to run other sub lets say to display a msg box each time the
function
is used in the sheet (not each time the function is calculated)
ie. only at the time the function is used in the sheet.
lets say in coding i want the following

Function myfunction (x as variant) as variant
myfunction = x+x
end function

sub mysub ()
'this should run each time the function
'my function is used in the cell
'not each time the function is calculated
msgbox "Myfunction is used in the active cell"
end sub

From workbook launch VBE using Alt+F11. From menu Insert a Module and paste
the below function.Close and get back to workbook and try the below formula.

=mysub(A1)
OR
=mysub(2)


Function mysub(argname As Variant) As Variant
mysub = argname * 2

If Application.Caller.Address = ActiveCell.Address Then Call macro
End Function

Sub macro()
MsgBox "ok"
End Sub

--
Jacob (MVP - Excel)


"Bob Phillips" wrote:


Maybe something like this

Public Sub Test()
MsgBox "hello"
End Sub

Function myTest(rng As Range)
If ActiveCell = Application.Caller Then Call Test
End Function

--

HTH

Bob

"Subodh" wrote in message
...
I have a custom function in Excel
I want to run other sub lets say to display a msg box each time the
function
is used in the sheet (not each time the function is calculated)
ie. only at the time the function is used in the sheet.
lets say in coding i want the following

Function myfunction (x as variant) as variant
myfunction = x+x
end function

sub mysub ()
'this should run each time the function
'my function is used in the cell
'not each time the function is calculated
msgbox "Myfunction is used in the active cell"
end sub


.

On May 6, 7:36*pm, Jacob Skaria
wrote:
From workbook launch VBE using Alt+F11. From menu Insert a Module and paste
the below function.Close and get back to workbook and try the below formula.

=mysub(A1)
OR
=mysub(2)

Function mysub(argname As Variant) As Variant
mysub = argname * 2

If Application.Caller.Address = ActiveCell.Address Then Call macro
End Function

Sub macro()
MsgBox "ok"
End Sub

--
Jacob (MVP - Excel)



"Bob Phillips" wrote:

Maybe something like this

Public Sub Test()
* * MsgBox "hello"
End Sub

Function myTest(rng As Range)
* * If ActiveCell = Application.Caller Then Call Test
End Function

--

HTH

Bob

"Subodh" wrote in message
....
I have a custom function in Excel
I want to run other sub lets say to display a msg box each time the
function
is used in the sheet (not each time the function is calculated)
ie. only at the time the function is used in the sheet.
lets say in coding i want the following

Function myfunction (x as variant) as variant
myfunction = x+x
end function

sub mysub ()
'this should run each time the function
'my function is used in the cell
'not each time the function is calculated
msgbox "Myfunction is used in the active cell"
end sub

.- Hide quoted text -

- Show quoted text -

Thanks Jacob
It worked well as i wanted.
It has been of great help to me
Thanks