Run a sub every time a function is used in a sheet

[Subodh]

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

 

[Bob Phillips]

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

 

[Jacob Skaria]

You can check whether the function is called from the activecell.....


Function GetSomething(strData As String)

'your code

If Application.Caller.Address = ActiveCell.Address Then
'function called from the activecell...
'do something
End If

End Function

 

[Subodh]

You can check whether the function is called from the activecell.....

Function GetSomething(strData As String)

'your code

If Application.Caller.Address = ActiveCell.Address Then
'function called from the activecell...
'do something
End If

End Function

--
Jacob (MVP - Excel)







- Show quoted text -

@ Bob
@ Jacob
Sorry I could not find out where to put the above code
either in the sheet or in the module
I tried both but it didn't worked.
I think i missed something.
Also, i couldn't determine which or how to define the range
Or, does it works for all the range
As desired, it is expected that it should work on every cells of the
workbook
Thanks for the prompt response.

 

[Bob Phillips]

We both assumed that you have a custom worksheet function (UDF), and so you
would add our suggestion into there.

It won't work with a built-in function.

--

HTH

Bob

You can check whether the function is called from the activecell.....

Function GetSomething(strData As String)

'your code

If Application.Caller.Address = ActiveCell.Address Then
'function called from the activecell...
'do something
End If

End Function

--
Jacob (MVP - Excel)







- Show quoted text -

@ Bob
@ Jacob
Sorry I could not find out where to put the above code
either in the sheet or in the module
I tried both but it didn't worked.
I think i missed something.
Also, i couldn't determine which or how to define the range
Or, does it works for all the range
As desired, it is expected that it should work on every cells of the
workbook
Thanks for the prompt response.

 

[Subodh]

We both assumed that you have a custom worksheet function (UDF), and so you
would add our suggestion into there.

It won't work with a built-in function.

--

HTH

Bob









@  Bob
@ Jacob
Sorry I could not find out where to put the above code
either in the sheet or in the module
I tried both but it didn't worked.
I think i missed something.
Also, i couldn't determine which or how to define the range
Or, does it works for all the range
As desired, it is expected that it should work on every cells of the
workbook
Thanks for the prompt response.- Hide quoted text -

- Show quoted text -

Yes i do have my own build in function.
I think i mentioned that in my first post also.
Lets say my function name is mysub with one argument
so my sub looks like this
function mysub (argname as variant) as variant
mysub= argname*2
................... 'more code
end function
' would you pleas let me know where should my custom function (UDF) be
inserted
Thanks anyway.

 

[Subodh]

Yes i do have my own build in function.
I think i mentioned that in my first post also.
Lets say my function name is mysub with one argument
so my sub looks like this
function mysub (argname as variant) as variant
mysub= argname*2
.................. 'more code
end function
' would you pleas let me know where should my custom function (UDF) be
inserted
Thanks anyway.- Hide quoted text -

- Show quoted text -

Plz add the following to the previous post.
I actually mean that or at least i expected that the code should have
some reference to the
mane of my function.
Where n how should i insert/add th reference to my function in the
above code.

 

[Jacob Skaria]

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 Skaria]

Hi "Subodh"

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

@Bob; sorry that I posted this as a response to your post..

 

[Subodh]

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)








- Show quoted text -

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