Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
problems with Worksheet_Change function when drop-down lists
Hi there
I've had this problem with an Excel program of mine for quite some time now. Have posted this problem to many news groups but haven't been able to find a solution yet. I finally was able to narrow down the problem, so I think it is now fairly well defined. I'm hoping this will help. Any help would be greatly appreciated! I have one column in my spreadsheet which has "data validation" set, so that there is a drop-down list of allowed pre-set values. The user can go in and change the contents of these cells by picking an item from the drop-down lists. When the user does this, the cell immediately to the right of it is filled out with some default parameters. I use the "Worksheet_Change" function to make this happen. Any time a cell changes in this particular column, a function is called which fills out the appropriate information in the neighboring column. Fine, everything works great so far. The problem comes in if I happen to call another function that I wrote in this same spreadsheet. I call this my "haunted" function... which makes the above function break... The functions seem completely unrelated, but perhaps I'm missing something. When I call this function from some other cell in the spreadsheet, nothing happens when I select an item from the drop-down lists mentioned above (the information that is supposed to be entered in neighboring column doesn't appear). Here is my "haunted" function: Public Function diffr() As Date Application.Volatile (True) With Application diffr = .ThisCell.Offset(0, -1).Value2 - .ThisCell.Offset(-1, -1).Value2 End With End Function This function simply calulates the difference between two dates/times, in the two cells immediately to the left and "up and to the left" of whichever cell calls the function. (There is a long story of why I wrote this function rather than just simply typing in the formula in the cell - but please ignore this for now.) It would be easier to just send my spreadsheet, which clearly shows the problem, but I don't see a way to do that here. If you need more information, please let me know. If any of you have any ideas what might be going on, Please let me know! Thank you!! Emma |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
problems with Worksheet_Change function when drop-down lists
Is the cell to the immediate left always a later date/time than the one up
and left? If not, it would result in a negative time which VBA might ignore. "Embirath" wrote: Hi there I've had this problem with an Excel program of mine for quite some time now. Have posted this problem to many news groups but haven't been able to find a solution yet. I finally was able to narrow down the problem, so I think it is now fairly well defined. I'm hoping this will help. Any help would be greatly appreciated! I have one column in my spreadsheet which has "data validation" set, so that there is a drop-down list of allowed pre-set values. The user can go in and change the contents of these cells by picking an item from the drop-down lists. When the user does this, the cell immediately to the right of it is filled out with some default parameters. I use the "Worksheet_Change" function to make this happen. Any time a cell changes in this particular column, a function is called which fills out the appropriate information in the neighboring column. Fine, everything works great so far. The problem comes in if I happen to call another function that I wrote in this same spreadsheet. I call this my "haunted" function... which makes the above function break... The functions seem completely unrelated, but perhaps I'm missing something. When I call this function from some other cell in the spreadsheet, nothing happens when I select an item from the drop-down lists mentioned above (the information that is supposed to be entered in neighboring column doesn't appear). Here is my "haunted" function: Public Function diffr() As Date Application.Volatile (True) With Application diffr = .ThisCell.Offset(0, -1).Value2 - .ThisCell.Offset(-1, -1).Value2 End With End Function This function simply calulates the difference between two dates/times, in the two cells immediately to the left and "up and to the left" of whichever cell calls the function. (There is a long story of why I wrote this function rather than just simply typing in the formula in the cell - but please ignore this for now.) It would be easier to just send my spreadsheet, which clearly shows the problem, but I don't see a way to do that here. If you need more information, please let me know. If any of you have any ideas what might be going on, Please let me know! Thank you!! Emma |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
problems with Worksheet_Change function when drop-down lists
Not sure if this applies, but Application.Caller (as was before
Application.ThisCell) could produce unexpected results, at least for me. Could be because you are not passing the 2 ranges as arguments. If you added a "debug.print .Thiscell.address", you may find it is being called from an unexpected cell. NickHK "Embirath" wrote in message ... Hi there I've had this problem with an Excel program of mine for quite some time now. Have posted this problem to many news groups but haven't been able to find a solution yet. I finally was able to narrow down the problem, so I think it is now fairly well defined. I'm hoping this will help. Any help would be greatly appreciated! I have one column in my spreadsheet which has "data validation" set, so that there is a drop-down list of allowed pre-set values. The user can go in and change the contents of these cells by picking an item from the drop-down lists. When the user does this, the cell immediately to the right of it is filled out with some default parameters. I use the "Worksheet_Change" function to make this happen. Any time a cell changes in this particular column, a function is called which fills out the appropriate information in the neighboring column. Fine, everything works great so far. The problem comes in if I happen to call another function that I wrote in this same spreadsheet. I call this my "haunted" function... which makes the above function break... The functions seem completely unrelated, but perhaps I'm missing something. When I call this function from some other cell in the spreadsheet, nothing happens when I select an item from the drop-down lists mentioned above (the information that is supposed to be entered in neighboring column doesn't appear). Here is my "haunted" function: Public Function diffr() As Date Application.Volatile (True) With Application diffr = .ThisCell.Offset(0, -1).Value2 - .ThisCell.Offset(-1, -1).Value2 End With End Function This function simply calulates the difference between two dates/times, in the two cells immediately to the left and "up and to the left" of whichever cell calls the function. (There is a long story of why I wrote this function rather than just simply typing in the formula in the cell - but please ignore this for now.) It would be easier to just send my spreadsheet, which clearly shows the problem, but I don't see a way to do that here. If you need more information, please let me know. If any of you have any ideas what might be going on, Please let me know! Thank you!! Emma |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
problems with Worksheet_Change function when drop-down lists
Hi JLGWhiz
Yes, the number calculated by this function should always be positive, so this is not really a problem. But it actually doesn't even matter if this function breaks and gives me an error. Just the fact that the function is being called within the spreadsheet breaks the other function, which should be totally unrelated. This is what is so confusing. thanks Emma "JLGWhiz" wrote: Is the cell to the immediate left always a later date/time than the one up and left? If not, it would result in a negative time which VBA might ignore. "Embirath" wrote: Hi there I've had this problem with an Excel program of mine for quite some time now. Have posted this problem to many news groups but haven't been able to find a solution yet. I finally was able to narrow down the problem, so I think it is now fairly well defined. I'm hoping this will help. Any help would be greatly appreciated! I have one column in my spreadsheet which has "data validation" set, so that there is a drop-down list of allowed pre-set values. The user can go in and change the contents of these cells by picking an item from the drop-down lists. When the user does this, the cell immediately to the right of it is filled out with some default parameters. I use the "Worksheet_Change" function to make this happen. Any time a cell changes in this particular column, a function is called which fills out the appropriate information in the neighboring column. Fine, everything works great so far. The problem comes in if I happen to call another function that I wrote in this same spreadsheet. I call this my "haunted" function... which makes the above function break... The functions seem completely unrelated, but perhaps I'm missing something. When I call this function from some other cell in the spreadsheet, nothing happens when I select an item from the drop-down lists mentioned above (the information that is supposed to be entered in neighboring column doesn't appear). Here is my "haunted" function: Public Function diffr() As Date Application.Volatile (True) With Application diffr = .ThisCell.Offset(0, -1).Value2 - .ThisCell.Offset(-1, -1).Value2 End With End Function This function simply calulates the difference between two dates/times, in the two cells immediately to the left and "up and to the left" of whichever cell calls the function. (There is a long story of why I wrote this function rather than just simply typing in the formula in the cell - but please ignore this for now.) It would be easier to just send my spreadsheet, which clearly shows the problem, but I don't see a way to do that here. If you need more information, please let me know. If any of you have any ideas what might be going on, Please let me know! Thank you!! Emma |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
problems with Worksheet_Change function when drop-down lists
Hi NickHK
Adding the debug.print call shows me the expected cell address. But, as I mentioned to JLGWhiz, I'm not too concerned about getting this function to work. It seems to work ok, except that it breaks another function which should be totally unrelated... It almost looks like this could be an Excel/VBA bug of some sort. I don't know. If you can think of some other method to use, to accomplish the same thing (ie filling in the neighboring column with some default values, when when the user selects an item from the drop-down list), then I'll be willing to try that. I can't seem to get around this one! :-( Thanks all of you for your time. Emma "NickHK" wrote: Not sure if this applies, but Application.Caller (as was before Application.ThisCell) could produce unexpected results, at least for me. Could be because you are not passing the 2 ranges as arguments. If you added a "debug.print .Thiscell.address", you may find it is being called from an unexpected cell. NickHK "Embirath" wrote in message ... Hi there I've had this problem with an Excel program of mine for quite some time now. Have posted this problem to many news groups but haven't been able to find a solution yet. I finally was able to narrow down the problem, so I think it is now fairly well defined. I'm hoping this will help. Any help would be greatly appreciated! I have one column in my spreadsheet which has "data validation" set, so that there is a drop-down list of allowed pre-set values. The user can go in and change the contents of these cells by picking an item from the drop-down lists. When the user does this, the cell immediately to the right of it is filled out with some default parameters. I use the "Worksheet_Change" function to make this happen. Any time a cell changes in this particular column, a function is called which fills out the appropriate information in the neighboring column. Fine, everything works great so far. The problem comes in if I happen to call another function that I wrote in this same spreadsheet. I call this my "haunted" function... which makes the above function break... The functions seem completely unrelated, but perhaps I'm missing something. When I call this function from some other cell in the spreadsheet, nothing happens when I select an item from the drop-down lists mentioned above (the information that is supposed to be entered in neighboring column doesn't appear). Here is my "haunted" function: Public Function diffr() As Date Application.Volatile (True) With Application diffr = .ThisCell.Offset(0, -1).Value2 - .ThisCell.Offset(-1, -1).Value2 End With End Function This function simply calulates the difference between two dates/times, in the two cells immediately to the left and "up and to the left" of whichever cell calls the function. (There is a long story of why I wrote this function rather than just simply typing in the formula in the cell - but please ignore this for now.) It would be easier to just send my spreadsheet, which clearly shows the problem, but I don't see a way to do that here. If you need more information, please let me know. If any of you have any ideas what might be going on, Please let me know! Thank you!! Emma |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
problems with Worksheet_Change function when drop-down lists
Hi Nick
For my curiosity, do you know what the difference is between ThisCell and Caller? Is there a difference? I'm wondering if there is a reason to update my programs to use "Caller". Thanks! Emma "NickHK" wrote: Not sure if this applies, but Application.Caller (as was before Application.ThisCell) could produce unexpected results, at least for me. Could be because you are not passing the 2 ranges as arguments. If you added a "debug.print .Thiscell.address", you may find it is being called from an unexpected cell. NickHK "Embirath" wrote in message ... Hi there I've had this problem with an Excel program of mine for quite some time now. Have posted this problem to many news groups but haven't been able to find a solution yet. I finally was able to narrow down the problem, so I think it is now fairly well defined. I'm hoping this will help. Any help would be greatly appreciated! I have one column in my spreadsheet which has "data validation" set, so that there is a drop-down list of allowed pre-set values. The user can go in and change the contents of these cells by picking an item from the drop-down lists. When the user does this, the cell immediately to the right of it is filled out with some default parameters. I use the "Worksheet_Change" function to make this happen. Any time a cell changes in this particular column, a function is called which fills out the appropriate information in the neighboring column. Fine, everything works great so far. The problem comes in if I happen to call another function that I wrote in this same spreadsheet. I call this my "haunted" function... which makes the above function break... The functions seem completely unrelated, but perhaps I'm missing something. When I call this function from some other cell in the spreadsheet, nothing happens when I select an item from the drop-down lists mentioned above (the information that is supposed to be entered in neighboring column doesn't appear). Here is my "haunted" function: Public Function diffr() As Date Application.Volatile (True) With Application diffr = .ThisCell.Offset(0, -1).Value2 - .ThisCell.Offset(-1, -1).Value2 End With End Function This function simply calulates the difference between two dates/times, in the two cells immediately to the left and "up and to the left" of whichever cell calls the function. (There is a long story of why I wrote this function rather than just simply typing in the formula in the cell - but please ignore this for now.) It would be easier to just send my spreadsheet, which clearly shows the problem, but I don't see a way to do that here. If you need more information, please let me know. If any of you have any ideas what might be going on, Please let me know! Thank you!! Emma |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
problems with Worksheet_Change function when drop-down lists
ThisCell was added after XL2000. Not sure of any drawbacks/benefits to the
change as I've not used it. ..Caller does not have to be cell; it depends on the reason the routine was called. To rule this routine out as the cause (or may be confirm it), you could change the routine to accept 2 dates as arguments and removed all the ..ThisCell.Offset code. Quite possible this is barking up the wrong tree, but .. NickHK "Embirath" wrote in message ... Hi Nick For my curiosity, do you know what the difference is between ThisCell and Caller? Is there a difference? I'm wondering if there is a reason to update my programs to use "Caller". Thanks! Emma "NickHK" wrote: Not sure if this applies, but Application.Caller (as was before Application.ThisCell) could produce unexpected results, at least for me. Could be because you are not passing the 2 ranges as arguments. If you added a "debug.print .Thiscell.address", you may find it is being called from an unexpected cell. NickHK "Embirath" wrote in message ... Hi there I've had this problem with an Excel program of mine for quite some time now. Have posted this problem to many news groups but haven't been able to find a solution yet. I finally was able to narrow down the problem, so I think it is now fairly well defined. I'm hoping this will help. Any help would be greatly appreciated! I have one column in my spreadsheet which has "data validation" set, so that there is a drop-down list of allowed pre-set values. The user can go in and change the contents of these cells by picking an item from the drop-down lists. When the user does this, the cell immediately to the right of it is filled out with some default parameters. I use the "Worksheet_Change" function to make this happen. Any time a cell changes in this particular column, a function is called which fills out the appropriate information in the neighboring column. Fine, everything works great so far. The problem comes in if I happen to call another function that I wrote in this same spreadsheet. I call this my "haunted" function... which makes the above function break... The functions seem completely unrelated, but perhaps I'm missing something. When I call this function from some other cell in the spreadsheet, nothing happens when I select an item from the drop-down lists mentioned above (the information that is supposed to be entered in neighboring column doesn't appear). Here is my "haunted" function: Public Function diffr() As Date Application.Volatile (True) With Application diffr = .ThisCell.Offset(0, -1).Value2 - ..ThisCell.Offset(-1, -1).Value2 End With End Function This function simply calulates the difference between two dates/times, in the two cells immediately to the left and "up and to the left" of whichever cell calls the function. (There is a long story of why I wrote this function rather than just simply typing in the formula in the cell - but please ignore this for now.) It would be easier to just send my spreadsheet, which clearly shows the problem, but I don't see a way to do that here. If you need more information, please let me know. If any of you have any ideas what might be going on, Please let me know! Thank you!! Emma |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
problems with Worksheet_Change function when drop-down lists
Hi again
The problem does go away when I don't use Application.ThisCell.Offset()... The problem is I need to use this function, so I need to make it work with it. The reason I don't want to use arguments in the function is because in the work I do, I must constantly rearrange (cut and copy/paste) rows. When there are arguments in the functions, cutting and pasting messes up the arguments. Excel thinks that I want to keep the old arguments when doing cut/paste, when I in fact just want the numbers to be calulated using cells at some relative location from the one being calculated. Can you think of a different way of doing this, without using the ThisCell property? It doesn't make any sense why the ThisCell property affects the Worksheet_Change function. Does this look like some kind of bug to you, or is there something about the ThisCell property that I don't understand? Btw, I did try using Caller instead of ThisCell, but it does not fix the problem. Thanks Emma "NickHK" wrote: ThisCell was added after XL2000. Not sure of any drawbacks/benefits to the change as I've not used it. ..Caller does not have to be cell; it depends on the reason the routine was called. To rule this routine out as the cause (or may be confirm it), you could change the routine to accept 2 dates as arguments and removed all the ..ThisCell.Offset code. Quite possible this is barking up the wrong tree, but .. NickHK "Embirath" wrote in message ... Hi Nick For my curiosity, do you know what the difference is between ThisCell and Caller? Is there a difference? I'm wondering if there is a reason to update my programs to use "Caller". Thanks! Emma "NickHK" wrote: Not sure if this applies, but Application.Caller (as was before Application.ThisCell) could produce unexpected results, at least for me. Could be because you are not passing the 2 ranges as arguments. If you added a "debug.print .Thiscell.address", you may find it is being called from an unexpected cell. NickHK "Embirath" wrote in message ... Hi there I've had this problem with an Excel program of mine for quite some time now. Have posted this problem to many news groups but haven't been able to find a solution yet. I finally was able to narrow down the problem, so I think it is now fairly well defined. I'm hoping this will help. Any help would be greatly appreciated! I have one column in my spreadsheet which has "data validation" set, so that there is a drop-down list of allowed pre-set values. The user can go in and change the contents of these cells by picking an item from the drop-down lists. When the user does this, the cell immediately to the right of it is filled out with some default parameters. I use the "Worksheet_Change" function to make this happen. Any time a cell changes in this particular column, a function is called which fills out the appropriate information in the neighboring column. Fine, everything works great so far. The problem comes in if I happen to call another function that I wrote in this same spreadsheet. I call this my "haunted" function... which makes the above function break... The functions seem completely unrelated, but perhaps I'm missing something. When I call this function from some other cell in the spreadsheet, nothing happens when I select an item from the drop-down lists mentioned above (the information that is supposed to be entered in neighboring column doesn't appear). Here is my "haunted" function: Public Function diffr() As Date Application.Volatile (True) With Application diffr = .ThisCell.Offset(0, -1).Value2 - ..ThisCell.Offset(-1, -1).Value2 End With End Function This function simply calulates the difference between two dates/times, in the two cells immediately to the left and "up and to the left" of whichever cell calls the function. (There is a long story of why I wrote this function rather than just simply typing in the formula in the cell - but please ignore this for now.) It would be easier to just send my spreadsheet, which clearly shows the problem, but I don't see a way to do that here. If you need more information, please let me know. If any of you have any ideas what might be going on, Please let me know! Thank you!! Emma |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
problems with Worksheet_Change function when drop-down lists
Trying to recreate your situation with:
<Module Public Function CallerTest() As String Application.Volatile CallerTest = Application.Caller.Offset(-1, 0).Address 'CallerTest = "Tested" End Function </Module <WS Private Sub Worksheet_Change(ByVal Target As Range) 'Application.EnableEvents = False If Not Intersect(Target, Range("Input")) Is Nothing Then Target.Offset(0, 1).Value = Asc(Target.Value) End If 'Application.EnableEvents = True End Sub </WS With the cell containing the data validation named "Input". With this, the WS_Change events does not fire. But comment out "Application.Volatile" and OK. So seems .Caller/ThisCell is not the direct culprit. Would that be a solution for you ? NickHK "Embirath" wrote in message ... Hi again The problem does go away when I don't use Application.ThisCell.Offset()... The problem is I need to use this function, so I need to make it work with it. The reason I don't want to use arguments in the function is because in the work I do, I must constantly rearrange (cut and copy/paste) rows. When there are arguments in the functions, cutting and pasting messes up the arguments. Excel thinks that I want to keep the old arguments when doing cut/paste, when I in fact just want the numbers to be calulated using cells at some relative location from the one being calculated. Can you think of a different way of doing this, without using the ThisCell property? It doesn't make any sense why the ThisCell property affects the Worksheet_Change function. Does this look like some kind of bug to you, or is there something about the ThisCell property that I don't understand? Btw, I did try using Caller instead of ThisCell, but it does not fix the problem. Thanks Emma "NickHK" wrote: ThisCell was added after XL2000. Not sure of any drawbacks/benefits to the change as I've not used it. ..Caller does not have to be cell; it depends on the reason the routine was called. To rule this routine out as the cause (or may be confirm it), you could change the routine to accept 2 dates as arguments and removed all the ..ThisCell.Offset code. Quite possible this is barking up the wrong tree, but .. NickHK "Embirath" wrote in message ... Hi Nick For my curiosity, do you know what the difference is between ThisCell and Caller? Is there a difference? I'm wondering if there is a reason to update my programs to use "Caller". Thanks! Emma "NickHK" wrote: Not sure if this applies, but Application.Caller (as was before Application.ThisCell) could produce unexpected results, at least for me. Could be because you are not passing the 2 ranges as arguments. If you added a "debug.print .Thiscell.address", you may find it is being called from an unexpected cell. NickHK "Embirath" wrote in message ... Hi there I've had this problem with an Excel program of mine for quite some time now. Have posted this problem to many news groups but haven't been able to find a solution yet. I finally was able to narrow down the problem, so I think it is now fairly well defined. I'm hoping this will help. Any help would be greatly appreciated! I have one column in my spreadsheet which has "data validation" set, so that there is a drop-down list of allowed pre-set values. The user can go in and change the contents of these cells by picking an item from the drop-down lists. When the user does this, the cell immediately to the right of it is filled out with some default parameters. I use the "Worksheet_Change" function to make this happen. Any time a cell changes in this particular column, a function is called which fills out the appropriate information in the neighboring column. Fine, everything works great so far. The problem comes in if I happen to call another function that I wrote in this same spreadsheet. I call this my "haunted" function... which makes the above function break... The functions seem completely unrelated, but perhaps I'm missing something. When I call this function from some other cell in the spreadsheet, nothing happens when I select an item from the drop-down lists mentioned above (the information that is supposed to be entered in neighboring column doesn't appear). Here is my "haunted" function: Public Function diffr() As Date Application.Volatile (True) With Application diffr = .ThisCell.Offset(0, -1).Value2 - ..ThisCell.Offset(-1, -1).Value2 End With End Function This function simply calulates the difference between two dates/times, in the two cells immediately to the left and "up and to the left" of whichever cell calls the function. (There is a long story of why I wrote this function rather than just simply typing in the formula in the cell - but please ignore this for now.) It would be easier to just send my spreadsheet, which clearly shows the problem, but I don't see a way to do that here. If you need more information, please let me know. If any of you have any ideas what might be going on, Please let me know! Thank you!! Emma |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
problems with Worksheet_Change function when drop-down lists
And I see the same for ThisCell instead of Caller.
The UDF is still calculated with each change of the data validation, without ..Volatile. So, seems to work as desired. This is with Excel 10. NickHK "NickHK" wrote in message ... Trying to recreate your situation with: <Module Public Function CallerTest() As String Application.Volatile CallerTest = Application.Caller.Offset(-1, 0).Address 'CallerTest = "Tested" End Function </Module <WS Private Sub Worksheet_Change(ByVal Target As Range) 'Application.EnableEvents = False If Not Intersect(Target, Range("Input")) Is Nothing Then Target.Offset(0, 1).Value = Asc(Target.Value) End If 'Application.EnableEvents = True End Sub </WS With the cell containing the data validation named "Input". With this, the WS_Change events does not fire. But comment out "Application.Volatile" and OK. So seems .Caller/ThisCell is not the direct culprit. Would that be a solution for you ? NickHK "Embirath" wrote in message ... Hi again The problem does go away when I don't use Application.ThisCell.Offset()... The problem is I need to use this function, so I need to make it work with it. The reason I don't want to use arguments in the function is because in the work I do, I must constantly rearrange (cut and copy/paste) rows. When there are arguments in the functions, cutting and pasting messes up the arguments. Excel thinks that I want to keep the old arguments when doing cut/paste, when I in fact just want the numbers to be calulated using cells at some relative location from the one being calculated. Can you think of a different way of doing this, without using the ThisCell property? It doesn't make any sense why the ThisCell property affects the Worksheet_Change function. Does this look like some kind of bug to you, or is there something about the ThisCell property that I don't understand? Btw, I did try using Caller instead of ThisCell, but it does not fix the problem. Thanks Emma "NickHK" wrote: ThisCell was added after XL2000. Not sure of any drawbacks/benefits to the change as I've not used it. ..Caller does not have to be cell; it depends on the reason the routine was called. To rule this routine out as the cause (or may be confirm it), you could change the routine to accept 2 dates as arguments and removed all the ..ThisCell.Offset code. Quite possible this is barking up the wrong tree, but .. NickHK "Embirath" wrote in message ... Hi Nick For my curiosity, do you know what the difference is between ThisCell and Caller? Is there a difference? I'm wondering if there is a reason to update my programs to use "Caller". Thanks! Emma "NickHK" wrote: Not sure if this applies, but Application.Caller (as was before Application.ThisCell) could produce unexpected results, at least for me. Could be because you are not passing the 2 ranges as arguments. If you added a "debug.print .Thiscell.address", you may find it is being called from an unexpected cell. NickHK "Embirath" wrote in message ... Hi there I've had this problem with an Excel program of mine for quite some time now. Have posted this problem to many news groups but haven't been able to find a solution yet. I finally was able to narrow down the problem, so I think it is now fairly well defined. I'm hoping this will help. Any help would be greatly appreciated! I have one column in my spreadsheet which has "data validation" set, so that there is a drop-down list of allowed pre-set values. The user can go in and change the contents of these cells by picking an item from the drop-down lists. When the user does this, the cell immediately to the right of it is filled out with some default parameters. I use the "Worksheet_Change" function to make this happen. Any time a cell changes in this particular column, a function is called which fills out the appropriate information in the neighboring column. Fine, everything works great so far. The problem comes in if I happen to call another function that I wrote in this same spreadsheet. I call this my "haunted" function... which makes the above function break... The functions seem completely unrelated, but perhaps I'm missing something. When I call this function from some other cell in the spreadsheet, nothing happens when I select an item from the drop-down lists mentioned above (the information that is supposed to be entered in neighboring column doesn't appear). Here is my "haunted" function: Public Function diffr() As Date Application.Volatile (True) With Application diffr = .ThisCell.Offset(0, -1).Value2 - ..ThisCell.Offset(-1, -1).Value2 End With End Function This function simply calulates the difference between two dates/times, in the two cells immediately to the left and "up and to the left" of whichever cell calls the function. (There is a long story of why I wrote this function rather than just simply typing in the formula in the cell - but please ignore this for now.) It would be easier to just send my spreadsheet, which clearly shows the problem, but I don't see a way to do that here. If you need more information, please let me know. If any of you have any ideas what might be going on, Please let me know! Thank you!! Emma |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
problems with Worksheet_Change function when drop-down lists
And XL2000 shows the same behaviour.
Does seem others have seen this. http://www.pcreview.co.uk/forums/thread-1016922.php Maybe the suggested .Calculation xlManual is better. NickHK "NickHK" wrote in message ... Trying to recreate your situation with: <Module Public Function CallerTest() As String Application.Volatile CallerTest = Application.Caller.Offset(-1, 0).Address 'CallerTest = "Tested" End Function </Module <WS Private Sub Worksheet_Change(ByVal Target As Range) 'Application.EnableEvents = False If Not Intersect(Target, Range("Input")) Is Nothing Then Target.Offset(0, 1).Value = Asc(Target.Value) End If 'Application.EnableEvents = True End Sub </WS With the cell containing the data validation named "Input". With this, the WS_Change events does not fire. But comment out "Application.Volatile" and OK. So seems .Caller/ThisCell is not the direct culprit. Would that be a solution for you ? NickHK "Embirath" wrote in message ... Hi again The problem does go away when I don't use Application.ThisCell.Offset()... The problem is I need to use this function, so I need to make it work with it. The reason I don't want to use arguments in the function is because in the work I do, I must constantly rearrange (cut and copy/paste) rows. When there are arguments in the functions, cutting and pasting messes up the arguments. Excel thinks that I want to keep the old arguments when doing cut/paste, when I in fact just want the numbers to be calulated using cells at some relative location from the one being calculated. Can you think of a different way of doing this, without using the ThisCell property? It doesn't make any sense why the ThisCell property affects the Worksheet_Change function. Does this look like some kind of bug to you, or is there something about the ThisCell property that I don't understand? Btw, I did try using Caller instead of ThisCell, but it does not fix the problem. Thanks Emma "NickHK" wrote: ThisCell was added after XL2000. Not sure of any drawbacks/benefits to the change as I've not used it. ..Caller does not have to be cell; it depends on the reason the routine was called. To rule this routine out as the cause (or may be confirm it), you could change the routine to accept 2 dates as arguments and removed all the ..ThisCell.Offset code. Quite possible this is barking up the wrong tree, but .. NickHK "Embirath" wrote in message ... Hi Nick For my curiosity, do you know what the difference is between ThisCell and Caller? Is there a difference? I'm wondering if there is a reason to update my programs to use "Caller". Thanks! Emma "NickHK" wrote: Not sure if this applies, but Application.Caller (as was before Application.ThisCell) could produce unexpected results, at least for me. Could be because you are not passing the 2 ranges as arguments. If you added a "debug.print .Thiscell.address", you may find it is being called from an unexpected cell. NickHK "Embirath" wrote in message ... Hi there I've had this problem with an Excel program of mine for quite some time now. Have posted this problem to many news groups but haven't been able to find a solution yet. I finally was able to narrow down the problem, so I think it is now fairly well defined. I'm hoping this will help. Any help would be greatly appreciated! I have one column in my spreadsheet which has "data validation" set, so that there is a drop-down list of allowed pre-set values. The user can go in and change the contents of these cells by picking an item from the drop-down lists. When the user does this, the cell immediately to the right of it is filled out with some default parameters. I use the "Worksheet_Change" function to make this happen. Any time a cell changes in this particular column, a function is called which fills out the appropriate information in the neighboring column. Fine, everything works great so far. The problem comes in if I happen to call another function that I wrote in this same spreadsheet. I call this my "haunted" function... which makes the above function break... The functions seem completely unrelated, but perhaps I'm missing something. When I call this function from some other cell in the spreadsheet, nothing happens when I select an item from the drop-down lists mentioned above (the information that is supposed to be entered in neighboring column doesn't appear). Here is my "haunted" function: Public Function diffr() As Date Application.Volatile (True) With Application diffr = .ThisCell.Offset(0, -1).Value2 - ..ThisCell.Offset(-1, -1).Value2 End With End Function This function simply calulates the difference between two dates/times, in the two cells immediately to the left and "up and to the left" of whichever cell calls the function. (There is a long story of why I wrote this function rather than just simply typing in the formula in the cell - but please ignore this for now.) It would be easier to just send my spreadsheet, which clearly shows the problem, but I don't see a way to do that here. If you need more information, please let me know. If any of you have any ideas what might be going on, Please let me know! Thank you!! Emma |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
problems with Worksheet_Change function when drop-down lists
Thanks a million, Nick. This was very helpful. It is all starting to make
sense to me now. I can simply remove the Application.Volatile statement. It would be nice if I could make it volatile, but oh well, I can live without it. :-) Thanks again. Emma "NickHK" wrote: And XL2000 shows the same behaviour. Does seem others have seen this. http://www.pcreview.co.uk/forums/thread-1016922.php Maybe the suggested .Calculation xlManual is better. NickHK "NickHK" wrote in message ... Trying to recreate your situation with: <Module Public Function CallerTest() As String Application.Volatile CallerTest = Application.Caller.Offset(-1, 0).Address 'CallerTest = "Tested" End Function </Module <WS Private Sub Worksheet_Change(ByVal Target As Range) 'Application.EnableEvents = False If Not Intersect(Target, Range("Input")) Is Nothing Then Target.Offset(0, 1).Value = Asc(Target.Value) End If 'Application.EnableEvents = True End Sub </WS With the cell containing the data validation named "Input". With this, the WS_Change events does not fire. But comment out "Application.Volatile" and OK. So seems .Caller/ThisCell is not the direct culprit. Would that be a solution for you ? NickHK "Embirath" wrote in message ... Hi again The problem does go away when I don't use Application.ThisCell.Offset()... The problem is I need to use this function, so I need to make it work with it. The reason I don't want to use arguments in the function is because in the work I do, I must constantly rearrange (cut and copy/paste) rows. When there are arguments in the functions, cutting and pasting messes up the arguments. Excel thinks that I want to keep the old arguments when doing cut/paste, when I in fact just want the numbers to be calulated using cells at some relative location from the one being calculated. Can you think of a different way of doing this, without using the ThisCell property? It doesn't make any sense why the ThisCell property affects the Worksheet_Change function. Does this look like some kind of bug to you, or is there something about the ThisCell property that I don't understand? Btw, I did try using Caller instead of ThisCell, but it does not fix the problem. Thanks Emma "NickHK" wrote: ThisCell was added after XL2000. Not sure of any drawbacks/benefits to the change as I've not used it. ..Caller does not have to be cell; it depends on the reason the routine was called. To rule this routine out as the cause (or may be confirm it), you could change the routine to accept 2 dates as arguments and removed all the ..ThisCell.Offset code. Quite possible this is barking up the wrong tree, but .. NickHK "Embirath" wrote in message ... Hi Nick For my curiosity, do you know what the difference is between ThisCell and Caller? Is there a difference? I'm wondering if there is a reason to update my programs to use "Caller". Thanks! Emma "NickHK" wrote: Not sure if this applies, but Application.Caller (as was before Application.ThisCell) could produce unexpected results, at least for me. Could be because you are not passing the 2 ranges as arguments. If you added a "debug.print .Thiscell.address", you may find it is being called from an unexpected cell. NickHK "Embirath" wrote in message ... Hi there I've had this problem with an Excel program of mine for quite some time now. Have posted this problem to many news groups but haven't been able to find a solution yet. I finally was able to narrow down the problem, so I think it is now fairly well defined. I'm hoping this will help. Any help would be greatly appreciated! I have one column in my spreadsheet which has "data validation" set, so that there is a drop-down list of allowed pre-set values. The user can go in and change the contents of these cells by picking an item from the drop-down lists. When the user does this, the cell immediately to the right of it is filled out with some default parameters. I use the "Worksheet_Change" function to make this happen. Any time a cell changes in this particular column, a function is called which fills out the appropriate information in the neighboring column. Fine, everything works great so far. The problem comes in if I happen to call another function that I wrote in this same spreadsheet. I call this my "haunted" function... which makes the above function break... The functions seem completely unrelated, but perhaps I'm missing something. When I call this function from some other cell in the spreadsheet, nothing happens when I select an item from the drop-down lists mentioned above (the information that is supposed to be entered in neighboring column doesn't appear). Here is my "haunted" function: Public Function diffr() As Date Application.Volatile (True) With Application diffr = .ThisCell.Offset(0, -1).Value2 - ..ThisCell.Offset(-1, -1).Value2 End With End Function This function simply calulates the difference between two dates/times, in the two cells immediately to the left and "up and to the left" of whichever cell calls the function. (There is a long story of why I wrote this function rather than just simply typing in the formula in the cell - but please ignore this for now.) It would be easier to just send my spreadsheet, which clearly shows the problem, but I don't see a way to do that here. If you need more information, please let me know. If any of you have any ideas what might be going on, Please let me know! Thank you!! Emma |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
problems with Worksheet_Change function when drop-down lists
Looks like my last posting didn't go through... Anyway, just wanted to say
THANKS A MILLION for your help on this. I will simply remove the Volatile statement. Thanks Emma "NickHK" wrote: And XL2000 shows the same behaviour. Does seem others have seen this. http://www.pcreview.co.uk/forums/thread-1016922.php Maybe the suggested .Calculation xlManual is better. NickHK "NickHK" wrote in message ... Trying to recreate your situation with: <Module Public Function CallerTest() As String Application.Volatile CallerTest = Application.Caller.Offset(-1, 0).Address 'CallerTest = "Tested" End Function </Module <WS Private Sub Worksheet_Change(ByVal Target As Range) 'Application.EnableEvents = False If Not Intersect(Target, Range("Input")) Is Nothing Then Target.Offset(0, 1).Value = Asc(Target.Value) End If 'Application.EnableEvents = True End Sub </WS With the cell containing the data validation named "Input". With this, the WS_Change events does not fire. But comment out "Application.Volatile" and OK. So seems .Caller/ThisCell is not the direct culprit. Would that be a solution for you ? NickHK "Embirath" wrote in message ... Hi again The problem does go away when I don't use Application.ThisCell.Offset()... The problem is I need to use this function, so I need to make it work with it. The reason I don't want to use arguments in the function is because in the work I do, I must constantly rearrange (cut and copy/paste) rows. When there are arguments in the functions, cutting and pasting messes up the arguments. Excel thinks that I want to keep the old arguments when doing cut/paste, when I in fact just want the numbers to be calulated using cells at some relative location from the one being calculated. Can you think of a different way of doing this, without using the ThisCell property? It doesn't make any sense why the ThisCell property affects the Worksheet_Change function. Does this look like some kind of bug to you, or is there something about the ThisCell property that I don't understand? Btw, I did try using Caller instead of ThisCell, but it does not fix the problem. Thanks Emma "NickHK" wrote: ThisCell was added after XL2000. Not sure of any drawbacks/benefits to the change as I've not used it. ..Caller does not have to be cell; it depends on the reason the routine was called. To rule this routine out as the cause (or may be confirm it), you could change the routine to accept 2 dates as arguments and removed all the ..ThisCell.Offset code. Quite possible this is barking up the wrong tree, but .. NickHK "Embirath" wrote in message ... Hi Nick For my curiosity, do you know what the difference is between ThisCell and Caller? Is there a difference? I'm wondering if there is a reason to update my programs to use "Caller". Thanks! Emma "NickHK" wrote: Not sure if this applies, but Application.Caller (as was before Application.ThisCell) could produce unexpected results, at least for me. Could be because you are not passing the 2 ranges as arguments. If you added a "debug.print .Thiscell.address", you may find it is being called from an unexpected cell. NickHK "Embirath" wrote in message ... Hi there I've had this problem with an Excel program of mine for quite some time now. Have posted this problem to many news groups but haven't been able to find a solution yet. I finally was able to narrow down the problem, so I think it is now fairly well defined. I'm hoping this will help. Any help would be greatly appreciated! I have one column in my spreadsheet which has "data validation" set, so that there is a drop-down list of allowed pre-set values. The user can go in and change the contents of these cells by picking an item from the drop-down lists. When the user does this, the cell immediately to the right of it is filled out with some default parameters. I use the "Worksheet_Change" function to make this happen. Any time a cell changes in this particular column, a function is called which fills out the appropriate information in the neighboring column. Fine, everything works great so far. The problem comes in if I happen to call another function that I wrote in this same spreadsheet. I call this my "haunted" function... which makes the above function break... The functions seem completely unrelated, but perhaps I'm missing something. When I call this function from some other cell in the spreadsheet, nothing happens when I select an item from the drop-down lists mentioned above (the information that is supposed to be entered in neighboring column doesn't appear). Here is my "haunted" function: Public Function diffr() As Date Application.Volatile (True) With Application diffr = .ThisCell.Offset(0, -1).Value2 - ..ThisCell.Offset(-1, -1).Value2 End With End Function This function simply calulates the difference between two dates/times, in the two cells immediately to the left and "up and to the left" of whichever cell calls the function. (There is a long story of why I wrote this function rather than just simply typing in the formula in the cell - but please ignore this for now.) It would be easier to just send my spreadsheet, which clearly shows the problem, but I don't see a way to do that here. If you need more information, please let me know. If any of you have any ideas what might be going on, Please let me know! Thank you!! Emma |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF function in drop down lists | Excel Discussion (Misc queries) | |||
Multiple lists with repeated values for dependet drop down lists | Excel Worksheet Functions | |||
Data validation function and drop-down lists | Excel Programming | |||
Problems with "Worksheet_Change" | Excel Discussion (Misc queries) | |||
worksheet_change problems | Excel Programming |