|
triggering Workbook_SheetCalculate
Hi All,
Is a Workbook_SheetCalculate event triggered by enabling/disabling a control, e.g. a commandbutton? I have one doing nothing else, and it seems to trigger itself again. Is it possible? If so, how can I avoid it to happen? Thanks, Stefi |
triggering Workbook_SheetCalculate
Hi Steffi.
Try posting the code for the CommandButton. --- Regards. Norman "Stefi" wrote in message ... Hi All, Is a Workbook_SheetCalculate event triggered by enabling/disabling a control, e.g. a commandbutton? I have one doing nothing else, and it seems to trigger itself again. Is it possible? If so, how can I avoid it to happen? Thanks, Stefi |
triggering Workbook_SheetCalculate
Hi Norman,
Im the meantime I discovered that Workbook_SheetCalculate event was triggered not by enabling/disabling a commandbutton but by a volatile UDF in some cells. Pressing F9 started Calculation (and triggered Workbook_SheetCalculate first time)therefore the volatile UDF was evaluated changing cell values, changing cell values started a new Calculation which triggered Workbook_SheetCalculate second time. The question is now how can I reduce No of executing Workbook_SheetCalculate to one? I do need volatile nature of the UDF! Regards, Stefi €žNorman Jones€ ezt Ã*rta: Hi Steffi. Try posting the code for the CommandButton. --- Regards. Norman "Stefi" wrote in message ... Hi All, Is a Workbook_SheetCalculate event triggered by enabling/disabling a control, e.g. a commandbutton? I have one doing nothing else, and it seems to trigger itself again. Is it possible? If so, how can I avoid it to happen? Thanks, Stefi |
triggering Workbook_SheetCalculate
Hi Steffi,
Without seeing your UDF, it is difficult to give a useful response. However, you can set the Application.Volatile statement to false in your UDF. Also, you should pass all references to cells as parameters to your UDF. In this way, the UDF will be recalculated whenever any of the cells change. This will obviate the need for an Application.Volatile statement which is slow. --- Regards. Norman "Stefi" wrote in message ... Hi Norman, Im the meantime I discovered that Workbook_SheetCalculate event was triggered not by enabling/disabling a commandbutton but by a volatile UDF in some cells. Pressing F9 started Calculation (and triggered Workbook_SheetCalculate first time)therefore the volatile UDF was evaluated changing cell values, changing cell values started a new Calculation which triggered Workbook_SheetCalculate second time. The question is now how can I reduce No of executing Workbook_SheetCalculate to one? I do need volatile nature of the UDF! Regards, Stefi €žNorman Jones€ ezt Ã*rta: Hi Steffi. Try posting the code for the CommandButton. --- Regards. Norman "Stefi" wrote in message ... Hi All, Is a Workbook_SheetCalculate event triggered by enabling/disabling a control, e.g. a commandbutton? I have one doing nothing else, and it seems to trigger itself again. Is it possible? If so, how can I avoid it to happen? Thanks, Stefi |
triggering Workbook_SheetCalculate
Hi Norman.
I tried to understand your suggestions, but I couldn't apply them to my case. So I post the code of Workbook_SheetCalculate and the UDF hoping you can give me some more useful advices. Private Sub Workbook_SheetCalculate(ByVal Sh As Object) Worksheets("Jelentés").CommandButton1.Enabled = _ Names("megvane").RefersToRange.Item(1, 1) And _ Names("megvane").RefersToRange.Item(1, 2) And _ Names("megvane").RefersToRange.Item(2, 1) And _ Names("megvane").RefersToRange.Item(2, 2) Worksheets("Jelentés").CommandButton3.Enabled = _ Names("megvane").RefersToRange.Item(2, 3) Worksheets("Jelentés").CommandButton2.Enabled = _ Names("megvane").RefersToRange.Item(3, 1) And _ Names("megvane").RefersToRange.Item(3, 2) Worksheets("Jelentés").CommandButton4.Enabled = _ Names("megvane").RefersToRange.Item(3, 3) End Sub The UDF: Public Function FileExists(fname, Optional most As Date) As Boolean Application.Volatile FileExists = False On Error Resume Next FileExists = IIf(Dir(fname) < "", True, False) End Function Range "megvane" is in another sheet named "segéd", range("G3:I5"), the first item (G3) contains =fileexists(Jelentés!$B3&Jelentés!G3) which is filled to the right and down throghout the range. Jelentés!$B3 contains folder name, Jelentés!G3 contains file name of files existence of which is to be checked. The do not change, therefore UDF is not recalculated. I'd like that the UDF be recalculated when pressing F9, to re-check the existence of the files. How can I do that without Volatile Regards, Stefi €žNorman Jones€ ezt Ã*rta: Hi Steffi, Without seeing your UDF, it is difficult to give a useful response. However, you can set the Application.Volatile statement to false in your UDF. Also, you should pass all references to cells as parameters to your UDF. In this way, the UDF will be recalculated whenever any of the cells change. This will obviate the need for an Application.Volatile statement which is slow. --- Regards. Norman "Stefi" wrote in message ... Hi Norman, Im the meantime I discovered that Workbook_SheetCalculate event was triggered not by enabling/disabling a commandbutton but by a volatile UDF in some cells. Pressing F9 started Calculation (and triggered Workbook_SheetCalculate first time)therefore the volatile UDF was evaluated changing cell values, changing cell values started a new Calculation which triggered Workbook_SheetCalculate second time. The question is now how can I reduce No of executing Workbook_SheetCalculate to one? I do need volatile nature of the UDF! Regards, Stefi €žNorman Jones€ ezt Ã*rta: Hi Steffi. Try posting the code for the CommandButton. --- Regards. Norman "Stefi" wrote in message ... Hi All, Is a Workbook_SheetCalculate event triggered by enabling/disabling a control, e.g. a commandbutton? I have one doing nothing else, and it seems to trigger itself again. Is it possible? If so, how can I avoid it to happen? Thanks, Stefi |
triggering Workbook_SheetCalculate
Hi Steffi,
I removed the Application Volatile instruction from your UDF. Changing the values of (say) cells B3 or G3 on the Jelentés worksheet updates the cell containing the formula: =fileexists(Jelentés!$B3&Jelentés!G3) on the Segéd sheet --- Regards. Norman "Stefi" wrote in message ... Hi Norman. I tried to understand your suggestions, but I couldn't apply them to my case. So I post the code of Workbook_SheetCalculate and the UDF hoping you can give me some more useful advices. Private Sub Workbook_SheetCalculate(ByVal Sh As Object) Worksheets("Jelentés").CommandButton1.Enabled = _ Names("megvane").RefersToRange.Item(1, 1) And _ Names("megvane").RefersToRange.Item(1, 2) And _ Names("megvane").RefersToRange.Item(2, 1) And _ Names("megvane").RefersToRange.Item(2, 2) Worksheets("Jelentés").CommandButton3.Enabled = _ Names("megvane").RefersToRange.Item(2, 3) Worksheets("Jelentés").CommandButton2.Enabled = _ Names("megvane").RefersToRange.Item(3, 1) And _ Names("megvane").RefersToRange.Item(3, 2) Worksheets("Jelentés").CommandButton4.Enabled = _ Names("megvane").RefersToRange.Item(3, 3) End Sub The UDF: Public Function FileExists(fname, Optional most As Date) As Boolean Application.Volatile FileExists = False On Error Resume Next FileExists = IIf(Dir(fname) < "", True, False) End Function Range "megvane" is in another sheet named "segéd", range("G3:I5"), the first item (G3) contains =fileexists(Jelentés!$B3&Jelentés!G3) which is filled to the right and down throghout the range. Jelentés!$B3 contains folder name, Jelentés!G3 contains file name of files existence of which is to be checked. The do not change, therefore UDF is not recalculated. I'd like that the UDF be recalculated when pressing F9, to re-check the existence of the files. How can I do that without Volatile Regards, Stefi |
triggering Workbook_SheetCalculate
Hi Stefi,
Not sure I understand exactly what you are trying to do, but it seems to me that you only want the workbook_sheetcalculate sub to run when FileExists has been calculated. The simplest way to do this is to create a WorkSheet_calculate sub in the code behind worksheet Seged Or you could check the name of the sheet inside the Private Sub Workbook_SheetCalculate(ByVal Sh As Object) if sh.name<"seged" then exit sub .... Charles __________________________________________________ The Excel Calculation Site http://www.decisionmodels.com "Stefi" wrote in message ... Hi Norman. I tried to understand your suggestions, but I couldn't apply them to my case. So I post the code of Workbook_SheetCalculate and the UDF hoping you can give me some more useful advices. Private Sub Workbook_SheetCalculate(ByVal Sh As Object) Worksheets("Jelentés").CommandButton1.Enabled = _ Names("megvane").RefersToRange.Item(1, 1) And _ Names("megvane").RefersToRange.Item(1, 2) And _ Names("megvane").RefersToRange.Item(2, 1) And _ Names("megvane").RefersToRange.Item(2, 2) Worksheets("Jelentés").CommandButton3.Enabled = _ Names("megvane").RefersToRange.Item(2, 3) Worksheets("Jelentés").CommandButton2.Enabled = _ Names("megvane").RefersToRange.Item(3, 1) And _ Names("megvane").RefersToRange.Item(3, 2) Worksheets("Jelentés").CommandButton4.Enabled = _ Names("megvane").RefersToRange.Item(3, 3) End Sub The UDF: Public Function FileExists(fname, Optional most As Date) As Boolean Application.Volatile FileExists = False On Error Resume Next FileExists = IIf(Dir(fname) < "", True, False) End Function Range "megvane" is in another sheet named "segéd", range("G3:I5"), the first item (G3) contains =fileexists(Jelentés!$B3&Jelentés!G3) which is filled to the right and down throghout the range. Jelentés!$B3 contains folder name, Jelentés!G3 contains file name of files existence of which is to be checked. The do not change, therefore UDF is not recalculated. I'd like that the UDF be recalculated when pressing F9, to re-check the existence of the files. How can I do that without Volatile Regards, Stefi "Norman Jones" ezt írta: Hi Steffi, Without seeing your UDF, it is difficult to give a useful response. However, you can set the Application.Volatile statement to false in your UDF. Also, you should pass all references to cells as parameters to your UDF. In this way, the UDF will be recalculated whenever any of the cells change. This will obviate the need for an Application.Volatile statement which is slow. --- Regards. Norman "Stefi" wrote in message ... Hi Norman, Im the meantime I discovered that Workbook_SheetCalculate event was triggered not by enabling/disabling a commandbutton but by a volatile UDF in some cells. Pressing F9 started Calculation (and triggered Workbook_SheetCalculate first time)therefore the volatile UDF was evaluated changing cell values, changing cell values started a new Calculation which triggered Workbook_SheetCalculate second time. The question is now how can I reduce No of executing Workbook_SheetCalculate to one? I do need volatile nature of the UDF! Regards, Stefi "Norman Jones" ezt írta: Hi Steffi. Try posting the code for the CommandButton. --- Regards. Norman "Stefi" wrote in message ... Hi All, Is a Workbook_SheetCalculate event triggered by enabling/disabling a control, e.g. a commandbutton? I have one doing nothing else, and it seems to trigger itself again. Is it possible? If so, how can I avoid it to happen? Thanks, Stefi |
triggering Workbook_SheetCalculate
Hi Charles,
Thank you for your post, I was giving up hope to solve this problem. I try to put clear the task: I have a range named "megvane" in sheet "seged" containing =Fileexists(...) UDFs. Fileexists is volatile because it is required to evaluate these cells when pressing F9. What I'd like to happen when pressing F9 is first to check the existence of files by evaluating =Fileexists(...) in range "megvane" then enabling/disabling commandbuttons depending on the values of range "megvane". The problem is that changing the values in range "megvane" also fires Workbook_SheetCalculate event so it is executed as many times as many cells range "megvane" has plus once for Calculate triggered by F9. I tried to place Application.EnableEvents = False as the first line of Workbook_SheetCalculate code, it solved the problem, but cannot find the place to switch it back to True. Placing it as the last line of Workbook_SheetCalculate code re creates the problem. Regards, Stefi €žCharles Williams€ ezt Ã*rta: Hi Stefi, Not sure I understand exactly what you are trying to do, but it seems to me that you only want the workbook_sheetcalculate sub to run when FileExists has been calculated. The simplest way to do this is to create a WorkSheet_calculate sub in the code behind worksheet Seged Or you could check the name of the sheet inside the Private Sub Workbook_SheetCalculate(ByVal Sh As Object) if sh.name<"seged" then exit sub .... Charles __________________________________________________ The Excel Calculation Site http://www.decisionmodels.com "Stefi" wrote in message ... Hi Norman. I tried to understand your suggestions, but I couldn't apply them to my case. So I post the code of Workbook_SheetCalculate and the UDF hoping you can give me some more useful advices. Private Sub Workbook_SheetCalculate(ByVal Sh As Object) Worksheets("Jelentés").CommandButton1.Enabled = _ Names("megvane").RefersToRange.Item(1, 1) And _ Names("megvane").RefersToRange.Item(1, 2) And _ Names("megvane").RefersToRange.Item(2, 1) And _ Names("megvane").RefersToRange.Item(2, 2) Worksheets("Jelentés").CommandButton3.Enabled = _ Names("megvane").RefersToRange.Item(2, 3) Worksheets("Jelentés").CommandButton2.Enabled = _ Names("megvane").RefersToRange.Item(3, 1) And _ Names("megvane").RefersToRange.Item(3, 2) Worksheets("Jelentés").CommandButton4.Enabled = _ Names("megvane").RefersToRange.Item(3, 3) End Sub The UDF: Public Function FileExists(fname, Optional most As Date) As Boolean Application.Volatile FileExists = False On Error Resume Next FileExists = IIf(Dir(fname) < "", True, False) End Function Range "megvane" is in another sheet named "segéd", range("G3:I5"), the first item (G3) contains =fileexists(Jelentés!$B3&Jelentés!G3) which is filled to the right and down throghout the range. Jelentés!$B3 contains folder name, Jelentés!G3 contains file name of files existence of which is to be checked. The do not change, therefore UDF is not recalculated. I'd like that the UDF be recalculated when pressing F9, to re-check the existence of the files. How can I do that without Volatile Regards, Stefi "Norman Jones" ezt Ã*rta: Hi Steffi, Without seeing your UDF, it is difficult to give a useful response. However, you can set the Application.Volatile statement to false in your UDF. Also, you should pass all references to cells as parameters to your UDF. In this way, the UDF will be recalculated whenever any of the cells change. This will obviate the need for an Application.Volatile statement which is slow. --- Regards. Norman "Stefi" wrote in message ... Hi Norman, Im the meantime I discovered that Workbook_SheetCalculate event was triggered not by enabling/disabling a commandbutton but by a volatile UDF in some cells. Pressing F9 started Calculation (and triggered Workbook_SheetCalculate first time)therefore the volatile UDF was evaluated changing cell values, changing cell values started a new Calculation which triggered Workbook_SheetCalculate second time. The question is now how can I reduce No of executing Workbook_SheetCalculate to one? I do need volatile nature of the UDF! Regards, Stefi "Norman Jones" ezt Ã*rta: Hi Steffi. Try posting the code for the CommandButton. --- Regards. Norman "Stefi" wrote in message ... Hi All, Is a Workbook_SheetCalculate event triggered by enabling/disabling a control, e.g. a commandbutton? I have one doing nothing else, and it seems to trigger itself again. Is it possible? If so, how can I avoid it to happen? Thanks, Stefi |
triggering Workbook_SheetCalculate
Hi Stefi,
I understand. Here is what you need to do. 1. copy the workbook_SheetCalculate code 2. In the VBE under VBAProject in the Project Window right-click Sheet "seged" and select view code 3. On the Seged code module that you have got to create a worksheet_Calculate sub 4. Paste in the code from Workbook_SheetCalculate 5. Remove the Workbook_SheetCalculate code. This will stop the workbook_Calculate sub from executing after every sheet recalcs, and make the code only calculate after the Seged sheet is calculated (which should be only once per F9). regards Charles __________________________________________________ The Excel Calculation Site http://www.decisionmodels.com "Stefi" wrote in message ... Hi Charles, Thank you for your post, I was giving up hope to solve this problem. I try to put clear the task: I have a range named "megvane" in sheet "seged" containing =Fileexists(...) UDFs. Fileexists is volatile because it is required to evaluate these cells when pressing F9. What I'd like to happen when pressing F9 is first to check the existence of files by evaluating =Fileexists(...) in range "megvane" then enabling/disabling commandbuttons depending on the values of range "megvane". The problem is that changing the values in range "megvane" also fires Workbook_SheetCalculate event so it is executed as many times as many cells range "megvane" has plus once for Calculate triggered by F9. I tried to place Application.EnableEvents = False as the first line of Workbook_SheetCalculate code, it solved the problem, but cannot find the place to switch it back to True. Placing it as the last line of Workbook_SheetCalculate code re creates the problem. Regards, Stefi "Charles Williams" ezt írta: Hi Stefi, Not sure I understand exactly what you are trying to do, but it seems to me that you only want the workbook_sheetcalculate sub to run when FileExists has been calculated. The simplest way to do this is to create a WorkSheet_calculate sub in the code behind worksheet Seged Or you could check the name of the sheet inside the Private Sub Workbook_SheetCalculate(ByVal Sh As Object) if sh.name<"seged" then exit sub .... Charles __________________________________________________ The Excel Calculation Site http://www.decisionmodels.com "Stefi" wrote in message ... Hi Norman. I tried to understand your suggestions, but I couldn't apply them to my case. So I post the code of Workbook_SheetCalculate and the UDF hoping you can give me some more useful advices. Private Sub Workbook_SheetCalculate(ByVal Sh As Object) Worksheets("Jelentés").CommandButton1.Enabled = _ Names("megvane").RefersToRange.Item(1, 1) And _ Names("megvane").RefersToRange.Item(1, 2) And _ Names("megvane").RefersToRange.Item(2, 1) And _ Names("megvane").RefersToRange.Item(2, 2) Worksheets("Jelentés").CommandButton3.Enabled = _ Names("megvane").RefersToRange.Item(2, 3) Worksheets("Jelentés").CommandButton2.Enabled = _ Names("megvane").RefersToRange.Item(3, 1) And _ Names("megvane").RefersToRange.Item(3, 2) Worksheets("Jelentés").CommandButton4.Enabled = _ Names("megvane").RefersToRange.Item(3, 3) End Sub The UDF: Public Function FileExists(fname, Optional most As Date) As Boolean Application.Volatile FileExists = False On Error Resume Next FileExists = IIf(Dir(fname) < "", True, False) End Function Range "megvane" is in another sheet named "segéd", range("G3:I5"), the first item (G3) contains =fileexists(Jelentés!$B3&Jelentés!G3) which is filled to the right and down throghout the range. Jelentés!$B3 contains folder name, Jelentés!G3 contains file name of files existence of which is to be checked. The do not change, therefore UDF is not recalculated. I'd like that the UDF be recalculated when pressing F9, to re-check the existence of the files. How can I do that without Volatile Regards, Stefi "Norman Jones" ezt írta: Hi Steffi, Without seeing your UDF, it is difficult to give a useful response. However, you can set the Application.Volatile statement to false in your UDF. Also, you should pass all references to cells as parameters to your UDF. In this way, the UDF will be recalculated whenever any of the cells change. This will obviate the need for an Application.Volatile statement which is slow. --- Regards. Norman "Stefi" wrote in message ... Hi Norman, Im the meantime I discovered that Workbook_SheetCalculate event was triggered not by enabling/disabling a commandbutton but by a volatile UDF in some cells. Pressing F9 started Calculation (and triggered Workbook_SheetCalculate first time)therefore the volatile UDF was evaluated changing cell values, changing cell values started a new Calculation which triggered Workbook_SheetCalculate second time. The question is now how can I reduce No of executing Workbook_SheetCalculate to one? I do need volatile nature of the UDF! Regards, Stefi "Norman Jones" ezt írta: Hi Steffi. Try posting the code for the CommandButton. --- Regards. Norman "Stefi" wrote in message ... Hi All, Is a Workbook_SheetCalculate event triggered by enabling/disabling a control, e.g. a commandbutton? I have one doing nothing else, and it seems to trigger itself again. Is it possible? If so, how can I avoid it to happen? Thanks, Stefi |
triggering Workbook_SheetCalculate
Many thanks, Charles, I'm going to try it and let you know the result.
Stefi €žCharles Williams€ ezt Ã*rta: Hi Stefi, I understand. Here is what you need to do. 1. copy the workbook_SheetCalculate code 2. In the VBE under VBAProject in the Project Window right-click Sheet "seged" and select view code 3. On the Seged code module that you have got to create a worksheet_Calculate sub 4. Paste in the code from Workbook_SheetCalculate 5. Remove the Workbook_SheetCalculate code. This will stop the workbook_Calculate sub from executing after every sheet recalcs, and make the code only calculate after the Seged sheet is calculated (which should be only once per F9). regards Charles __________________________________________________ The Excel Calculation Site http://www.decisionmodels.com "Stefi" wrote in message ... Hi Charles, Thank you for your post, I was giving up hope to solve this problem. I try to put clear the task: I have a range named "megvane" in sheet "seged" containing =Fileexists(...) UDFs. Fileexists is volatile because it is required to evaluate these cells when pressing F9. What I'd like to happen when pressing F9 is first to check the existence of files by evaluating =Fileexists(...) in range "megvane" then enabling/disabling commandbuttons depending on the values of range "megvane". The problem is that changing the values in range "megvane" also fires Workbook_SheetCalculate event so it is executed as many times as many cells range "megvane" has plus once for Calculate triggered by F9. I tried to place Application.EnableEvents = False as the first line of Workbook_SheetCalculate code, it solved the problem, but cannot find the place to switch it back to True. Placing it as the last line of Workbook_SheetCalculate code re creates the problem. Regards, Stefi "Charles Williams" ezt Ã*rta: Hi Stefi, Not sure I understand exactly what you are trying to do, but it seems to me that you only want the workbook_sheetcalculate sub to run when FileExists has been calculated. The simplest way to do this is to create a WorkSheet_calculate sub in the code behind worksheet Seged Or you could check the name of the sheet inside the Private Sub Workbook_SheetCalculate(ByVal Sh As Object) if sh.name<"seged" then exit sub .... Charles __________________________________________________ The Excel Calculation Site http://www.decisionmodels.com "Stefi" wrote in message ... Hi Norman. I tried to understand your suggestions, but I couldn't apply them to my case. So I post the code of Workbook_SheetCalculate and the UDF hoping you can give me some more useful advices. Private Sub Workbook_SheetCalculate(ByVal Sh As Object) Worksheets("Jelentés").CommandButton1.Enabled = _ Names("megvane").RefersToRange.Item(1, 1) And _ Names("megvane").RefersToRange.Item(1, 2) And _ Names("megvane").RefersToRange.Item(2, 1) And _ Names("megvane").RefersToRange.Item(2, 2) Worksheets("Jelentés").CommandButton3.Enabled = _ Names("megvane").RefersToRange.Item(2, 3) Worksheets("Jelentés").CommandButton2.Enabled = _ Names("megvane").RefersToRange.Item(3, 1) And _ Names("megvane").RefersToRange.Item(3, 2) Worksheets("Jelentés").CommandButton4.Enabled = _ Names("megvane").RefersToRange.Item(3, 3) End Sub The UDF: Public Function FileExists(fname, Optional most As Date) As Boolean Application.Volatile FileExists = False On Error Resume Next FileExists = IIf(Dir(fname) < "", True, False) End Function Range "megvane" is in another sheet named "segéd", range("G3:I5"), the first item (G3) contains =fileexists(Jelentés!$B3&Jelentés!G3) which is filled to the right and down throghout the range. Jelentés!$B3 contains folder name, Jelentés!G3 contains file name of files existence of which is to be checked. The do not change, therefore UDF is not recalculated. I'd like that the UDF be recalculated when pressing F9, to re-check the existence of the files. How can I do that without Volatile Regards, Stefi "Norman Jones" ezt Ã*rta: Hi Steffi, Without seeing your UDF, it is difficult to give a useful response. However, you can set the Application.Volatile statement to false in your UDF. Also, you should pass all references to cells as parameters to your UDF. In this way, the UDF will be recalculated whenever any of the cells change. This will obviate the need for an Application.Volatile statement which is slow. --- Regards. Norman "Stefi" wrote in message ... Hi Norman, Im the meantime I discovered that Workbook_SheetCalculate event was triggered not by enabling/disabling a commandbutton but by a volatile UDF in some cells. Pressing F9 started Calculation (and triggered Workbook_SheetCalculate first time)therefore the volatile UDF was evaluated changing cell values, changing cell values started a new Calculation which triggered Workbook_SheetCalculate second time. The question is now how can I reduce No of executing Workbook_SheetCalculate to one? I do need volatile nature of the UDF! Regards, Stefi "Norman Jones" ezt Ã*rta: Hi Steffi. Try posting the code for the CommandButton. --- Regards. Norman "Stefi" wrote in message ... Hi All, Is a Workbook_SheetCalculate event triggered by enabling/disabling a control, e.g. a commandbutton? I have one doing nothing else, and it seems to trigger itself again. Is it possible? If so, how can I avoid it to happen? Thanks, Stefi |
triggering Workbook_SheetCalculate
Hi Charles,
Your suggestion almost solved my problem. One question remained: pressing F9 doesn't fire Worksheet_Calculate of Seged if nothing else changes but the existence of files. Any further idea? Regards, Stefi €žStefi€ ezt Ã*rta: Many thanks, Charles, I'm going to try it and let you know the result. Stefi €žCharles Williams€ ezt Ã*rta: Hi Stefi, I understand. Here is what you need to do. 1. copy the workbook_SheetCalculate code 2. In the VBE under VBAProject in the Project Window right-click Sheet "seged" and select view code 3. On the Seged code module that you have got to create a worksheet_Calculate sub 4. Paste in the code from Workbook_SheetCalculate 5. Remove the Workbook_SheetCalculate code. This will stop the workbook_Calculate sub from executing after every sheet recalcs, and make the code only calculate after the Seged sheet is calculated (which should be only once per F9). regards Charles __________________________________________________ The Excel Calculation Site http://www.decisionmodels.com "Stefi" wrote in message ... Hi Charles, Thank you for your post, I was giving up hope to solve this problem. I try to put clear the task: I have a range named "megvane" in sheet "seged" containing =Fileexists(...) UDFs. Fileexists is volatile because it is required to evaluate these cells when pressing F9. What I'd like to happen when pressing F9 is first to check the existence of files by evaluating =Fileexists(...) in range "megvane" then enabling/disabling commandbuttons depending on the values of range "megvane". The problem is that changing the values in range "megvane" also fires Workbook_SheetCalculate event so it is executed as many times as many cells range "megvane" has plus once for Calculate triggered by F9. I tried to place Application.EnableEvents = False as the first line of Workbook_SheetCalculate code, it solved the problem, but cannot find the place to switch it back to True. Placing it as the last line of Workbook_SheetCalculate code re creates the problem. Regards, Stefi "Charles Williams" ezt Ã*rta: Hi Stefi, Not sure I understand exactly what you are trying to do, but it seems to me that you only want the workbook_sheetcalculate sub to run when FileExists has been calculated. The simplest way to do this is to create a WorkSheet_calculate sub in the code behind worksheet Seged Or you could check the name of the sheet inside the Private Sub Workbook_SheetCalculate(ByVal Sh As Object) if sh.name<"seged" then exit sub .... Charles __________________________________________________ The Excel Calculation Site http://www.decisionmodels.com "Stefi" wrote in message ... Hi Norman. I tried to understand your suggestions, but I couldn't apply them to my case. So I post the code of Workbook_SheetCalculate and the UDF hoping you can give me some more useful advices. Private Sub Workbook_SheetCalculate(ByVal Sh As Object) Worksheets("Jelentés").CommandButton1.Enabled = _ Names("megvane").RefersToRange.Item(1, 1) And _ Names("megvane").RefersToRange.Item(1, 2) And _ Names("megvane").RefersToRange.Item(2, 1) And _ Names("megvane").RefersToRange.Item(2, 2) Worksheets("Jelentés").CommandButton3.Enabled = _ Names("megvane").RefersToRange.Item(2, 3) Worksheets("Jelentés").CommandButton2.Enabled = _ Names("megvane").RefersToRange.Item(3, 1) And _ Names("megvane").RefersToRange.Item(3, 2) Worksheets("Jelentés").CommandButton4.Enabled = _ Names("megvane").RefersToRange.Item(3, 3) End Sub The UDF: Public Function FileExists(fname, Optional most As Date) As Boolean Application.Volatile FileExists = False On Error Resume Next FileExists = IIf(Dir(fname) < "", True, False) End Function Range "megvane" is in another sheet named "segéd", range("G3:I5"), the first item (G3) contains =fileexists(Jelentés!$B3&Jelentés!G3) which is filled to the right and down throghout the range. Jelentés!$B3 contains folder name, Jelentés!G3 contains file name of files existence of which is to be checked. The do not change, therefore UDF is not recalculated. I'd like that the UDF be recalculated when pressing F9, to re-check the existence of the files. How can I do that without Volatile Regards, Stefi "Norman Jones" ezt Ã*rta: Hi Steffi, Without seeing your UDF, it is difficult to give a useful response. However, you can set the Application.Volatile statement to false in your UDF. Also, you should pass all references to cells as parameters to your UDF. In this way, the UDF will be recalculated whenever any of the cells change. This will obviate the need for an Application.Volatile statement which is slow. --- Regards. Norman "Stefi" wrote in message ... Hi Norman, Im the meantime I discovered that Workbook_SheetCalculate event was triggered not by enabling/disabling a commandbutton but by a volatile UDF in some cells. Pressing F9 started Calculation (and triggered Workbook_SheetCalculate first time)therefore the volatile UDF was evaluated changing cell values, changing cell values started a new Calculation which triggered Workbook_SheetCalculate second time. The question is now how can I reduce No of executing Workbook_SheetCalculate to one? I do need volatile nature of the UDF! Regards, Stefi "Norman Jones" ezt Ã*rta: Hi Steffi. Try posting the code for the CommandButton. --- Regards. Norman "Stefi" wrote in message ... Hi All, Is a Workbook_SheetCalculate event triggered by enabling/disabling a control, e.g. a commandbutton? I have one doing nothing else, and it seems to trigger itself again. Is it possible? If so, how can I avoid it to happen? Thanks, Stefi |
triggering Workbook_SheetCalculate
Are you sure your fileexists function is ?
- volatile - used on worksheet Seged Charles __________________________________________________ The Excel Calculation Site http://www.decisionmodels.com "Stefi" wrote in message ... Hi Charles, Your suggestion almost solved my problem. One question remained: pressing F9 doesn't fire Worksheet_Calculate of Seged if nothing else changes but the existence of files. Any further idea? Regards, Stefi "Stefi" ezt írta: Many thanks, Charles, I'm going to try it and let you know the result. Stefi "Charles Williams" ezt írta: Hi Stefi, I understand. Here is what you need to do. 1. copy the workbook_SheetCalculate code 2. In the VBE under VBAProject in the Project Window right-click Sheet "seged" and select view code 3. On the Seged code module that you have got to create a worksheet_Calculate sub 4. Paste in the code from Workbook_SheetCalculate 5. Remove the Workbook_SheetCalculate code. This will stop the workbook_Calculate sub from executing after every sheet recalcs, and make the code only calculate after the Seged sheet is calculated (which should be only once per F9). regards Charles __________________________________________________ The Excel Calculation Site http://www.decisionmodels.com "Stefi" wrote in message ... Hi Charles, Thank you for your post, I was giving up hope to solve this problem. I try to put clear the task: I have a range named "megvane" in sheet "seged" containing =Fileexists(...) UDFs. Fileexists is volatile because it is required to evaluate these cells when pressing F9. What I'd like to happen when pressing F9 is first to check the existence of files by evaluating =Fileexists(...) in range "megvane" then enabling/disabling commandbuttons depending on the values of range "megvane". The problem is that changing the values in range "megvane" also fires Workbook_SheetCalculate event so it is executed as many times as many cells range "megvane" has plus once for Calculate triggered by F9. I tried to place Application.EnableEvents = False as the first line of Workbook_SheetCalculate code, it solved the problem, but cannot find the place to switch it back to True. Placing it as the last line of Workbook_SheetCalculate code re creates the problem. Regards, Stefi "Charles Williams" ezt írta: Hi Stefi, Not sure I understand exactly what you are trying to do, but it seems to me that you only want the workbook_sheetcalculate sub to run when FileExists has been calculated. The simplest way to do this is to create a WorkSheet_calculate sub in the code behind worksheet Seged Or you could check the name of the sheet inside the Private Sub Workbook_SheetCalculate(ByVal Sh As Object) if sh.name<"seged" then exit sub .... Charles __________________________________________________ The Excel Calculation Site http://www.decisionmodels.com "Stefi" wrote in message ... Hi Norman. I tried to understand your suggestions, but I couldn't apply them to my case. So I post the code of Workbook_SheetCalculate and the UDF hoping you can give me some more useful advices. Private Sub Workbook_SheetCalculate(ByVal Sh As Object) Worksheets("Jelentés").CommandButton1.Enabled = _ Names("megvane").RefersToRange.Item(1, 1) And _ Names("megvane").RefersToRange.Item(1, 2) And _ Names("megvane").RefersToRange.Item(2, 1) And _ Names("megvane").RefersToRange.Item(2, 2) Worksheets("Jelentés").CommandButton3.Enabled = _ Names("megvane").RefersToRange.Item(2, 3) Worksheets("Jelentés").CommandButton2.Enabled = _ Names("megvane").RefersToRange.Item(3, 1) And _ Names("megvane").RefersToRange.Item(3, 2) Worksheets("Jelentés").CommandButton4.Enabled = _ Names("megvane").RefersToRange.Item(3, 3) End Sub The UDF: Public Function FileExists(fname, Optional most As Date) As Boolean Application.Volatile FileExists = False On Error Resume Next FileExists = IIf(Dir(fname) < "", True, False) End Function Range "megvane" is in another sheet named "segéd", range("G3:I5"), the first item (G3) contains =fileexists(Jelentés!$B3&Jelentés!G3) which is filled to the right and down throghout the range. Jelentés!$B3 contains folder name, Jelentés!G3 contains file name of files existence of which is to be checked. The do not change, therefore UDF is not recalculated. I'd like that the UDF be recalculated when pressing F9, to re-check the existence of the files. How can I do that without Volatile Regards, Stefi "Norman Jones" ezt írta: Hi Steffi, Without seeing your UDF, it is difficult to give a useful response. However, you can set the Application.Volatile statement to false in your UDF. Also, you should pass all references to cells as parameters to your UDF. In this way, the UDF will be recalculated whenever any of the cells change. This will obviate the need for an Application.Volatile statement which is slow. --- Regards. Norman "Stefi" wrote in message ... Hi Norman, Im the meantime I discovered that Workbook_SheetCalculate event was triggered not by enabling/disabling a commandbutton but by a volatile UDF in some cells. Pressing F9 started Calculation (and triggered Workbook_SheetCalculate first time)therefore the volatile UDF was evaluated changing cell values, changing cell values started a new Calculation which triggered Workbook_SheetCalculate second time. The question is now how can I reduce No of executing Workbook_SheetCalculate to one? I do need volatile nature of the UDF! Regards, Stefi "Norman Jones" ezt írta: Hi Steffi. Try posting the code for the CommandButton. --- Regards. Norman "Stefi" wrote in message ... Hi All, Is a Workbook_SheetCalculate event triggered by enabling/disabling a control, e.g. a commandbutton? I have one doing nothing else, and it seems to trigger itself again. Is it possible? If so, how can I avoid it to happen? Thanks, Stefi |
triggering Workbook_SheetCalculate
Oh god, it is, I checked it again, and during the repeated test I discovered
the cause of the problem: a control (checkbox) was active and not a worksheet. Having clicked on the worksheet F9 worked again as prescribed. What do you think, what is the easiest way to avoid this situation? Stefi €žCharles Williams€ ezt Ã*rta: Are you sure your fileexists function is ? - volatile - used on worksheet Seged Charles __________________________________________________ The Excel Calculation Site http://www.decisionmodels.com "Stefi" wrote in message ... Hi Charles, Your suggestion almost solved my problem. One question remained: pressing F9 doesn't fire Worksheet_Calculate of Seged if nothing else changes but the existence of files. Any further idea? Regards, Stefi "Stefi" ezt Ã*rta: Many thanks, Charles, I'm going to try it and let you know the result. Stefi "Charles Williams" ezt Ã*rta: Hi Stefi, I understand. Here is what you need to do. 1. copy the workbook_SheetCalculate code 2. In the VBE under VBAProject in the Project Window right-click Sheet "seged" and select view code 3. On the Seged code module that you have got to create a worksheet_Calculate sub 4. Paste in the code from Workbook_SheetCalculate 5. Remove the Workbook_SheetCalculate code. This will stop the workbook_Calculate sub from executing after every sheet recalcs, and make the code only calculate after the Seged sheet is calculated (which should be only once per F9). regards Charles __________________________________________________ The Excel Calculation Site http://www.decisionmodels.com "Stefi" wrote in message ... Hi Charles, Thank you for your post, I was giving up hope to solve this problem. I try to put clear the task: I have a range named "megvane" in sheet "seged" containing =Fileexists(...) UDFs. Fileexists is volatile because it is required to evaluate these cells when pressing F9. What I'd like to happen when pressing F9 is first to check the existence of files by evaluating =Fileexists(...) in range "megvane" then enabling/disabling commandbuttons depending on the values of range "megvane". The problem is that changing the values in range "megvane" also fires Workbook_SheetCalculate event so it is executed as many times as many cells range "megvane" has plus once for Calculate triggered by F9. I tried to place Application.EnableEvents = False as the first line of Workbook_SheetCalculate code, it solved the problem, but cannot find the place to switch it back to True. Placing it as the last line of Workbook_SheetCalculate code re creates the problem. Regards, Stefi "Charles Williams" ezt Ã*rta: Hi Stefi, Not sure I understand exactly what you are trying to do, but it seems to me that you only want the workbook_sheetcalculate sub to run when FileExists has been calculated. The simplest way to do this is to create a WorkSheet_calculate sub in the code behind worksheet Seged Or you could check the name of the sheet inside the Private Sub Workbook_SheetCalculate(ByVal Sh As Object) if sh.name<"seged" then exit sub .... Charles __________________________________________________ The Excel Calculation Site http://www.decisionmodels.com "Stefi" wrote in message ... Hi Norman. I tried to understand your suggestions, but I couldn't apply them to my case. So I post the code of Workbook_SheetCalculate and the UDF hoping you can give me some more useful advices. Private Sub Workbook_SheetCalculate(ByVal Sh As Object) Worksheets("Jelentés").CommandButton1.Enabled = _ Names("megvane").RefersToRange.Item(1, 1) And _ Names("megvane").RefersToRange.Item(1, 2) And _ Names("megvane").RefersToRange.Item(2, 1) And _ Names("megvane").RefersToRange.Item(2, 2) Worksheets("Jelentés").CommandButton3.Enabled = _ Names("megvane").RefersToRange.Item(2, 3) Worksheets("Jelentés").CommandButton2.Enabled = _ Names("megvane").RefersToRange.Item(3, 1) And _ Names("megvane").RefersToRange.Item(3, 2) Worksheets("Jelentés").CommandButton4.Enabled = _ Names("megvane").RefersToRange.Item(3, 3) End Sub The UDF: Public Function FileExists(fname, Optional most As Date) As Boolean Application.Volatile FileExists = False On Error Resume Next FileExists = IIf(Dir(fname) < "", True, False) End Function Range "megvane" is in another sheet named "segéd", range("G3:I5"), the first item (G3) contains =fileexists(Jelentés!$B3&Jelentés!G3) which is filled to the right and down throghout the range. Jelentés!$B3 contains folder name, Jelentés!G3 contains file name of files existence of which is to be checked. The do not change, therefore UDF is not recalculated. I'd like that the UDF be recalculated when pressing F9, to re-check the existence of the files. How can I do that without Volatile Regards, Stefi "Norman Jones" ezt Ã*rta: Hi Steffi, Without seeing your UDF, it is difficult to give a useful response. However, you can set the Application.Volatile statement to false in your UDF. Also, you should pass all references to cells as parameters to your UDF. In this way, the UDF will be recalculated whenever any of the cells change. This will obviate the need for an Application.Volatile statement which is slow. --- Regards. Norman "Stefi" wrote in message ... Hi Norman, Im the meantime I discovered that Workbook_SheetCalculate event was triggered not by enabling/disabling a commandbutton but by a volatile UDF in some cells. Pressing F9 started Calculation (and triggered Workbook_SheetCalculate first time)therefore the volatile UDF was evaluated changing cell values, changing cell values started a new Calculation which triggered Workbook_SheetCalculate second time. The question is now how can I reduce No of executing Workbook_SheetCalculate to one? I do need volatile nature of the UDF! Regards, Stefi "Norman Jones" ezt Ã*rta: Hi Steffi. Try posting the code for the CommandButton. --- Regards. Norman "Stefi" wrote in message ... Hi All, Is a Workbook_SheetCalculate event triggered by enabling/disabling a control, e.g. a commandbutton? I have one doing nothing else, and it seems to trigger itself again. Is it possible? If so, how can I avoid it to happen? Thanks, Stefi |
| All times are GMT +1. The time now is 03:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com