Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run a sub every time a function is used in a sheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run a sub every time a function is used in a sheet
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run a sub every time a function is used in a sheet
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) "Subodh" wrote: 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run a sub every time a function is used in a sheet
On May 6, 2:09*pm, Jacob Skaria
wrote: 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) "Subodh" wrote: 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 - @ 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run a sub every time a function is used in a sheet
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 "Subodh" wrote in message ... On May 6, 2:09 pm, Jacob Skaria wrote: 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) "Subodh" wrote: 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 - @ 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run a sub every time a function is used in a sheet
On May 6, 4:53*pm, "Bob Phillips" wrote:
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 "Subodh" wrote in message ... On May 6, 2:09 pm, Jacob Skaria wrote: 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) "Subodh" wrote: 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 - @ *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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run a sub every time a function is used in a sheet
On May 6, 6:15*pm, Subodh wrote:
On May 6, 4:53*pm, "Bob Phillips" wrote: 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 "Subodh" wrote in message .... On May 6, 2:09 pm, Jacob Skaria wrote: 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) "Subodh" wrote: 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 - @ *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.- 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run a sub every time a function is used in a sheet
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 . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run a sub every time a function is used in a sheet
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.. -- Jacob (MVP - Excel) "Subodh" wrote: On May 6, 4:53 pm, "Bob Phillips" wrote: 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 "Subodh" wrote in message ... On May 6, 2:09 pm, Jacob Skaria wrote: 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) "Subodh" wrote: 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 - @ 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. . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run a sub every time a function is used in a sheet
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time Sheet - Calculating Time Differences for Totals | Excel Discussion (Misc queries) | |||
Calender function - time sheet - excel 2003/7 | Excel Worksheet Functions | |||
verify use of TIME Function, Find Quantity Level compare to time-d | Excel Discussion (Misc queries) | |||
Detailed Time Sheet (overtime, comp time, vacation used) | New Users to Excel | |||
Time Sheet Function | Excel Worksheet Functions |