Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA message box to inform user that Excel is still calculating
Hi - I am new to VBA and am trying to figure out how to get a message box to
pop-up whenever Excel calculates so the user waits and does not interrupt the calculation process. Specifically, it would remain on the screen until the calculation process stops. Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA message box to inform user that Excel is still calculating
Excel should not take but a second to calculate everything. I'm not sure why
you want to do what you are wanting to do. Do you have a macro that is taking a long time to run? If so, here is a tip to speed up your macro. Sub YourMacro() With Application .ScreenUpdating = False .Calculation = xlCalculationManual .StatusBar = "Excel is still calculating...Please Wait." End With ' your code here With Application .ScreenUpdating = True .Calculation = xlCalculationAutomatic .StatusBar = False End With End Sub Note: You can't easily use a MsgBox to tell the user Excel is still calculating. But you can change the text in the Status Bar in the lower left corner in Excel. Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "Dan" wrote: Hi - I am new to VBA and am trying to figure out how to get a message box to pop-up whenever Excel calculates so the user waits and does not interrupt the calculation process. Specifically, it would remain on the screen until the calculation process stops. Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA message box to inform user that Excel is still calculating
Hi Ryan,
Thanks for the reply - I appreciate it. The reason behind my original question is a pretty sizable model that I have built around a couple of data sources that link to a data warehouse (downloaded into Pivot and data tables within the model). The model takes raw data from numerous sources and combines it into a simple user-friendly analytical format that the user can manipulate via simple drop-downs. Given the amount of data involved, a couple of the user selections on the Dashboard tab require about 10-20 seconds for Excel to calculate. However, users are sometimes too impatient and will start clicking on the worksheet if the calc is not instantaneous, which will interrupt the calculation process. Hence, my desire to have a window pop-up that informs the user that calculation is going on and to be patient (the Excel status bar in the lower-right hand corner is not obvious enough). I have discovered the following VBA code (see below) that initiates a pop-up window upon calculation, however, I cannot get the window to disappear once calculation stops (have tried a few IF THEN statements, but in vain) - the only way to get rid of it with the code below is to manually close it, which i do not want the user to have to do... Private Sub Worksheet_Calculate() msg = "Calculating...Please Wait" MsgBox msg Do Loop Until Application.CalculationState = xlDone ' End Sub Is there a way to get the message box to automatically go away once calculation has stopped? Also, I have created a UserForm (UserForm1) to replace the message box (so I can format it), however, i cannot get the message to show-up in the user form, only the UserForm itself - can you help? Thanks in advance! Dan "Ryan H" wrote: Excel should not take but a second to calculate everything. I'm not sure why you want to do what you are wanting to do. Do you have a macro that is taking a long time to run? If so, here is a tip to speed up your macro. Sub YourMacro() With Application .ScreenUpdating = False .Calculation = xlCalculationManual .StatusBar = "Excel is still calculating...Please Wait." End With ' your code here With Application .ScreenUpdating = True .Calculation = xlCalculationAutomatic .StatusBar = False End With End Sub Note: You can't easily use a MsgBox to tell the user Excel is still calculating. But you can change the text in the Status Bar in the lower left corner in Excel. Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "Dan" wrote: Hi - I am new to VBA and am trying to figure out how to get a message box to pop-up whenever Excel calculates so the user waits and does not interrupt the calculation process. Specifically, it would remain on the screen until the calculation process stops. Thanks! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA message box to inform user that Excel is still calculating
You should be able to accomplish what you are looking for using a Userform.
I will assume you just have a Label on the userform that says, "Excel is still calculating...Please Wait." or something like that. Since I can't duplicate your pivot table situation I was not able to test this code. So let me know if you have any issues. Please give specifics of the errors if any. Let me know if this helps, click "YES" below. Put this in the worksheet that is being calculated. This code will show the userform when the worksheet beings to calculate. Private Sub Worksheet_Calculate() UserForm1.Show End Sub Put this in the userform module. This code will fire when the userform is shown. It will continue the loop until Excel is done calculating. Private Sub UserForm_Activate() Do If Application.CalculationState = xlDone Then Unload UserForm1 Exit Do End If Loop End Sub -- Cheers, Ryan "Dan" wrote: Hi Ryan, Thanks for the reply - I appreciate it. The reason behind my original question is a pretty sizable model that I have built around a couple of data sources that link to a data warehouse (downloaded into Pivot and data tables within the model). The model takes raw data from numerous sources and combines it into a simple user-friendly analytical format that the user can manipulate via simple drop-downs. Given the amount of data involved, a couple of the user selections on the Dashboard tab require about 10-20 seconds for Excel to calculate. However, users are sometimes too impatient and will start clicking on the worksheet if the calc is not instantaneous, which will interrupt the calculation process. Hence, my desire to have a window pop-up that informs the user that calculation is going on and to be patient (the Excel status bar in the lower-right hand corner is not obvious enough). I have discovered the following VBA code (see below) that initiates a pop-up window upon calculation, however, I cannot get the window to disappear once calculation stops (have tried a few IF THEN statements, but in vain) - the only way to get rid of it with the code below is to manually close it, which i do not want the user to have to do... Private Sub Worksheet_Calculate() msg = "Calculating...Please Wait" MsgBox msg Do Loop Until Application.CalculationState = xlDone ' End Sub Is there a way to get the message box to automatically go away once calculation has stopped? Also, I have created a UserForm (UserForm1) to replace the message box (so I can format it), however, i cannot get the message to show-up in the user form, only the UserForm itself - can you help? Thanks in advance! Dan "Ryan H" wrote: Excel should not take but a second to calculate everything. I'm not sure why you want to do what you are wanting to do. Do you have a macro that is taking a long time to run? If so, here is a tip to speed up your macro. Sub YourMacro() With Application .ScreenUpdating = False .Calculation = xlCalculationManual .StatusBar = "Excel is still calculating...Please Wait." End With ' your code here With Application .ScreenUpdating = True .Calculation = xlCalculationAutomatic .StatusBar = False End With End Sub Note: You can't easily use a MsgBox to tell the user Excel is still calculating. But you can change the text in the Status Bar in the lower left corner in Excel. Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "Dan" wrote: Hi - I am new to VBA and am trying to figure out how to get a message box to pop-up whenever Excel calculates so the user waits and does not interrupt the calculation process. Specifically, it would remain on the screen until the calculation process stops. Thanks! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA message box to inform user that Excel is still calculating
You may also want to try this. Let me know if this helps, if so, click "YES"
below. Put this in the worksheet that calculations are taking place. Private Sub Worksheet_Calculate() UserForm1.Show End Sub Put this in the userfrom module. Private Sub UserForm_Activate() Call WaitTimer End Sub Put this code in a standard module. Sub WaitTimer() If Application.CalculationState < xlDone Then Application.OnTime EarliestTime:=Now + TimeValue("00:00:01"), _ Procedu="WaitTimer", Schedule:=True Else Unload UserForm1 End If End Sub -- Cheers, Ryan "Dan" wrote: Hi Ryan, Thanks for the reply - I appreciate it. The reason behind my original question is a pretty sizable model that I have built around a couple of data sources that link to a data warehouse (downloaded into Pivot and data tables within the model). The model takes raw data from numerous sources and combines it into a simple user-friendly analytical format that the user can manipulate via simple drop-downs. Given the amount of data involved, a couple of the user selections on the Dashboard tab require about 10-20 seconds for Excel to calculate. However, users are sometimes too impatient and will start clicking on the worksheet if the calc is not instantaneous, which will interrupt the calculation process. Hence, my desire to have a window pop-up that informs the user that calculation is going on and to be patient (the Excel status bar in the lower-right hand corner is not obvious enough). I have discovered the following VBA code (see below) that initiates a pop-up window upon calculation, however, I cannot get the window to disappear once calculation stops (have tried a few IF THEN statements, but in vain) - the only way to get rid of it with the code below is to manually close it, which i do not want the user to have to do... Private Sub Worksheet_Calculate() msg = "Calculating...Please Wait" MsgBox msg Do Loop Until Application.CalculationState = xlDone ' End Sub Is there a way to get the message box to automatically go away once calculation has stopped? Also, I have created a UserForm (UserForm1) to replace the message box (so I can format it), however, i cannot get the message to show-up in the user form, only the UserForm itself - can you help? Thanks in advance! Dan "Ryan H" wrote: Excel should not take but a second to calculate everything. I'm not sure why you want to do what you are wanting to do. Do you have a macro that is taking a long time to run? If so, here is a tip to speed up your macro. Sub YourMacro() With Application .ScreenUpdating = False .Calculation = xlCalculationManual .StatusBar = "Excel is still calculating...Please Wait." End With ' your code here With Application .ScreenUpdating = True .Calculation = xlCalculationAutomatic .StatusBar = False End With End Sub Note: You can't easily use a MsgBox to tell the user Excel is still calculating. But you can change the text in the Status Bar in the lower left corner in Excel. Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "Dan" wrote: Hi - I am new to VBA and am trying to figure out how to get a message box to pop-up whenever Excel calculates so the user waits and does not interrupt the calculation process. Specifically, it would remain on the screen until the calculation process stops. Thanks! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA message box to inform user that Excel is still calculating
Hi Ryan,
Thanks again for all of your help with this - I do have a follow-up though: The macro that you wrote worked great except that it only fires once calculation is complete (so the window pops-up just after calculation stops). My goal is to get the window to pop-up once calculation starts and then go away after it stops so the user doesn't start clicking on the worksheet and interrupt the calc process. Is there some way to start the userform when calculation state = xlpending or xlcalculating? and then have it stop once calculation is complete? Again - I appreciate all of your time! Thanks, Dan "Ryan H" wrote: You should be able to accomplish what you are looking for using a Userform. I will assume you just have a Label on the userform that says, "Excel is still calculating...Please Wait." or something like that. Since I can't duplicate your pivot table situation I was not able to test this code. So let me know if you have any issues. Please give specifics of the errors if any. Let me know if this helps, click "YES" below. Put this in the worksheet that is being calculated. This code will show the userform when the worksheet beings to calculate. Private Sub Worksheet_Calculate() UserForm1.Show End Sub Put this in the userform module. This code will fire when the userform is shown. It will continue the loop until Excel is done calculating. Private Sub UserForm_Activate() Do If Application.CalculationState = xlDone Then Unload UserForm1 Exit Do End If Loop End Sub -- Cheers, Ryan "Dan" wrote: Hi Ryan, Thanks for the reply - I appreciate it. The reason behind my original question is a pretty sizable model that I have built around a couple of data sources that link to a data warehouse (downloaded into Pivot and data tables within the model). The model takes raw data from numerous sources and combines it into a simple user-friendly analytical format that the user can manipulate via simple drop-downs. Given the amount of data involved, a couple of the user selections on the Dashboard tab require about 10-20 seconds for Excel to calculate. However, users are sometimes too impatient and will start clicking on the worksheet if the calc is not instantaneous, which will interrupt the calculation process. Hence, my desire to have a window pop-up that informs the user that calculation is going on and to be patient (the Excel status bar in the lower-right hand corner is not obvious enough). I have discovered the following VBA code (see below) that initiates a pop-up window upon calculation, however, I cannot get the window to disappear once calculation stops (have tried a few IF THEN statements, but in vain) - the only way to get rid of it with the code below is to manually close it, which i do not want the user to have to do... Private Sub Worksheet_Calculate() msg = "Calculating...Please Wait" MsgBox msg Do Loop Until Application.CalculationState = xlDone ' End Sub Is there a way to get the message box to automatically go away once calculation has stopped? Also, I have created a UserForm (UserForm1) to replace the message box (so I can format it), however, i cannot get the message to show-up in the user form, only the UserForm itself - can you help? Thanks in advance! Dan "Ryan H" wrote: Excel should not take but a second to calculate everything. I'm not sure why you want to do what you are wanting to do. Do you have a macro that is taking a long time to run? If so, here is a tip to speed up your macro. Sub YourMacro() With Application .ScreenUpdating = False .Calculation = xlCalculationManual .StatusBar = "Excel is still calculating...Please Wait." End With ' your code here With Application .ScreenUpdating = True .Calculation = xlCalculationAutomatic .StatusBar = False End With End Sub Note: You can't easily use a MsgBox to tell the user Excel is still calculating. But you can change the text in the Status Bar in the lower left corner in Excel. Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "Dan" wrote: Hi - I am new to VBA and am trying to figure out how to get a message box to pop-up whenever Excel calculates so the user waits and does not interrupt the calculation process. Specifically, it would remain on the screen until the calculation process stops. Thanks! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA message box to inform user that Excel is still calculating
Hi Ryan,
My workbook will calc, then show the UserForm, then calc again...and again. I have to manually get rid of the UserForm (however, Excel continues to calc). There are a few worksheets in the workbook with formulas, so I'm not sure if that makes a difference. I really can't send you the file as it contains confidential info. Unless you have any more suggestions, I guess that I'll have to throw in the towel on this one. I'll check back just in case, but if you want to move onto other posts, then I'll say thanks for all of your time. I'm also not sure if you get points when people hit the 'yes', but I will as a thanks for your time & effort. Thanks again, Dan "Ryan H" wrote: Excel must be calculating before it shows the userform. I'm not sure if we can stop that. Give this a try. This set of code will turn off calculation mode, show your userform, calculate, then unload the userform (in theory). Private Sub Worksheet_Calculate() Application.Calculation = xlCalculationManual UserForm1.Show End Sub Private Sub UserForm_Initialize() Application.Calculate Do If Application.CalculationState = xlDone Then Application.Calculation = xlCalculationAutomatic Unload UserForm1 Exit Do End If Loop End Sub If this doesn't work send me an e-mail to with your workbook and I will take a look at it. I may be able to turn off calculation mode to the workbook and take advantage of some pivot tabel events. Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "Dan" wrote: Hi Ryan, Thanks again for all of your help with this - I do have a follow-up though: The macro that you wrote worked great except that it only fires once calculation is complete (so the window pops-up just after calculation stops). My goal is to get the window to pop-up once calculation starts and then go away after it stops so the user doesn't start clicking on the worksheet and interrupt the calc process. Is there some way to start the userform when calculation state = xlpending or xlcalculating? and then have it stop once calculation is complete? Again - I appreciate all of your time! Thanks, Dan "Ryan H" wrote: You should be able to accomplish what you are looking for using a Userform. I will assume you just have a Label on the userform that says, "Excel is still calculating...Please Wait." or something like that. Since I can't duplicate your pivot table situation I was not able to test this code. So let me know if you have any issues. Please give specifics of the errors if any. Let me know if this helps, click "YES" below. Put this in the worksheet that is being calculated. This code will show the userform when the worksheet beings to calculate. Private Sub Worksheet_Calculate() UserForm1.Show End Sub Put this in the userform module. This code will fire when the userform is shown. It will continue the loop until Excel is done calculating. Private Sub UserForm_Activate() Do If Application.CalculationState = xlDone Then Unload UserForm1 Exit Do End If Loop End Sub -- Cheers, Ryan "Dan" wrote: Hi Ryan, Thanks for the reply - I appreciate it. The reason behind my original question is a pretty sizable model that I have built around a couple of data sources that link to a data warehouse (downloaded into Pivot and data tables within the model). The model takes raw data from numerous sources and combines it into a simple user-friendly analytical format that the user can manipulate via simple drop-downs. Given the amount of data involved, a couple of the user selections on the Dashboard tab require about 10-20 seconds for Excel to calculate. However, users are sometimes too impatient and will start clicking on the worksheet if the calc is not instantaneous, which will interrupt the calculation process. Hence, my desire to have a window pop-up that informs the user that calculation is going on and to be patient (the Excel status bar in the lower-right hand corner is not obvious enough). I have discovered the following VBA code (see below) that initiates a pop-up window upon calculation, however, I cannot get the window to disappear once calculation stops (have tried a few IF THEN statements, but in vain) - the only way to get rid of it with the code below is to manually close it, which i do not want the user to have to do... Private Sub Worksheet_Calculate() msg = "Calculating...Please Wait" MsgBox msg Do Loop Until Application.CalculationState = xlDone ' End Sub Is there a way to get the message box to automatically go away once calculation has stopped? Also, I have created a UserForm (UserForm1) to replace the message box (so I can format it), however, i cannot get the message to show-up in the user form, only the UserForm itself - can you help? Thanks in advance! Dan "Ryan H" wrote: Excel should not take but a second to calculate everything. I'm not sure why you want to do what you are wanting to do. Do you have a macro that is taking a long time to run? If so, here is a tip to speed up your macro. Sub YourMacro() With Application .ScreenUpdating = False .Calculation = xlCalculationManual .StatusBar = "Excel is still calculating...Please Wait." End With ' your code here With Application .ScreenUpdating = True .Calculation = xlCalculationAutomatic .StatusBar = False End With End Sub Note: You can't easily use a MsgBox to tell the user Excel is still calculating. But you can change the text in the Status Bar in the lower left corner in Excel. Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "Dan" wrote: Hi - I am new to VBA and am trying to figure out how to get a message box to pop-up whenever Excel calculates so the user waits and does not interrupt the calculation process. Specifically, it would remain on the screen until the calculation process stops. Thanks! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA message box to inform user that Excel is still calculating
Since I can't see your situation I am left to guessing. Give this situation
a try. 1. Set Calculate Mode to Manual for the Workbook. 2. Put this code in the worksheet module the pivot table is located. I assume the name of your pivot table is "PivotTable1". This code will fire each time the pivot table is updated. Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) If Target.Name = "PivotTable1" Then UserForm1.Show End Sub 3. Put this code in the Userform module. The code will now manually calculate your workbooks. And Excel will test if it is still calculating every 1 second. If its not, it should unload the userform. Private Sub UserForm_Initialize() Application.Calculate Call WaitTimer End Sub Private Sub WaitTimer() If Application.CalculationState < xlDone Then Application.OnTime EarliestTime:=Now + TimeValue("00:00:01"), _ Procedu="WaitTimer", Schedule:=True Else Unload UserForm1 End If End Sub 4. And yes, I guess you could say we get points. I have helped over 100 people so I get this nice little silver medal next to my name. The next step is a gold medal, but I must help 500 people. Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "Dan" wrote: Hi Ryan, My workbook will calc, then show the UserForm, then calc again...and again. I have to manually get rid of the UserForm (however, Excel continues to calc). There are a few worksheets in the workbook with formulas, so I'm not sure if that makes a difference. I really can't send you the file as it contains confidential info. Unless you have any more suggestions, I guess that I'll have to throw in the towel on this one. I'll check back just in case, but if you want to move onto other posts, then I'll say thanks for all of your time. I'm also not sure if you get points when people hit the 'yes', but I will as a thanks for your time & effort. Thanks again, Dan "Ryan H" wrote: Excel must be calculating before it shows the userform. I'm not sure if we can stop that. Give this a try. This set of code will turn off calculation mode, show your userform, calculate, then unload the userform (in theory). Private Sub Worksheet_Calculate() Application.Calculation = xlCalculationManual UserForm1.Show End Sub Private Sub UserForm_Initialize() Application.Calculate Do If Application.CalculationState = xlDone Then Application.Calculation = xlCalculationAutomatic Unload UserForm1 Exit Do End If Loop End Sub If this doesn't work send me an e-mail to with your workbook and I will take a look at it. I may be able to turn off calculation mode to the workbook and take advantage of some pivot tabel events. Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "Dan" wrote: Hi Ryan, Thanks again for all of your help with this - I do have a follow-up though: The macro that you wrote worked great except that it only fires once calculation is complete (so the window pops-up just after calculation stops). My goal is to get the window to pop-up once calculation starts and then go away after it stops so the user doesn't start clicking on the worksheet and interrupt the calc process. Is there some way to start the userform when calculation state = xlpending or xlcalculating? and then have it stop once calculation is complete? Again - I appreciate all of your time! Thanks, Dan "Ryan H" wrote: You should be able to accomplish what you are looking for using a Userform. I will assume you just have a Label on the userform that says, "Excel is still calculating...Please Wait." or something like that. Since I can't duplicate your pivot table situation I was not able to test this code. So let me know if you have any issues. Please give specifics of the errors if any. Let me know if this helps, click "YES" below. Put this in the worksheet that is being calculated. This code will show the userform when the worksheet beings to calculate. Private Sub Worksheet_Calculate() UserForm1.Show End Sub Put this in the userform module. This code will fire when the userform is shown. It will continue the loop until Excel is done calculating. Private Sub UserForm_Activate() Do If Application.CalculationState = xlDone Then Unload UserForm1 Exit Do End If Loop End Sub -- Cheers, Ryan "Dan" wrote: Hi Ryan, Thanks for the reply - I appreciate it. The reason behind my original question is a pretty sizable model that I have built around a couple of data sources that link to a data warehouse (downloaded into Pivot and data tables within the model). The model takes raw data from numerous sources and combines it into a simple user-friendly analytical format that the user can manipulate via simple drop-downs. Given the amount of data involved, a couple of the user selections on the Dashboard tab require about 10-20 seconds for Excel to calculate. However, users are sometimes too impatient and will start clicking on the worksheet if the calc is not instantaneous, which will interrupt the calculation process. Hence, my desire to have a window pop-up that informs the user that calculation is going on and to be patient (the Excel status bar in the lower-right hand corner is not obvious enough). I have discovered the following VBA code (see below) that initiates a pop-up window upon calculation, however, I cannot get the window to disappear once calculation stops (have tried a few IF THEN statements, but in vain) - the only way to get rid of it with the code below is to manually close it, which i do not want the user to have to do... Private Sub Worksheet_Calculate() msg = "Calculating...Please Wait" MsgBox msg Do Loop Until Application.CalculationState = xlDone ' End Sub Is there a way to get the message box to automatically go away once calculation has stopped? Also, I have created a UserForm (UserForm1) to replace the message box (so I can format it), however, i cannot get the message to show-up in the user form, only the UserForm itself - can you help? Thanks in advance! Dan "Ryan H" wrote: Excel should not take but a second to calculate everything. I'm not sure why you want to do what you are wanting to do. Do you have a macro that is taking a long time to run? If so, here is a tip to speed up your macro. Sub YourMacro() With Application .ScreenUpdating = False .Calculation = xlCalculationManual .StatusBar = "Excel is still calculating...Please Wait." End With ' your code here With Application .ScreenUpdating = True .Calculation = xlCalculationAutomatic .StatusBar = False End With End Sub Note: You can't easily use a MsgBox to tell the user Excel is still calculating. But you can change the text in the Status Bar in the lower left corner in Excel. Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "Dan" wrote: Hi - I am new to VBA and am trying to figure out how to get a message box to pop-up whenever Excel calculates so the user waits and does not interrupt the calculation process. Specifically, it would remain on the screen until the calculation process stops. Thanks! |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA message box to inform user that Excel is still calculating
Hi, I am having a similar issue.
I want to prevent the user from interrumpting the "application.calculate" command. Auto calc is off to start, in the button, I set it to manual and save the current calc mode state. Throughout the subroutnie in the button I execute application.calcualte several times and they can take a few minutes. The issues that if the user click the sheet during this application.calculate the application stops the calculate and continues to the next statement. Dan, Did you ever settle on a good fix for this? Thanks, Jeff On Feb 18, 3:24*pm, Ryan H wrote: Since I can't see your situation I am left to guessing. *Give this situation a try. 1. *Set Calculate Mode to Manual for the Workbook. 2. *Put this code in the worksheet module the pivot table is located. *I assume the name of your pivot table is "PivotTable1". *This code will fire each time the pivot table is updated. Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) * * If Target.Name = "PivotTable1" Then UserForm1.Show End Sub 3. *Put this code in the Userform module. *The code will now manually calculate your workbooks. *And Excel will test if it is still calculating every 1 second. *If its not, it should unload the userform. Private Sub UserForm_Initialize() * * Application.Calculate * * Call WaitTimer End Sub Private Sub WaitTimer() * * If Application.CalculationState < xlDone Then * * * * Application.OnTime EarliestTime:=Now + TimeValue("00:00:01"), _ * * * * * * * * * * * * * * Procedu="WaitTimer", Schedule:=True * * Else * * * * Unload UserForm1 * * End If End Sub 4. *And yes, I guess you could say we get points. *I have helped over 100 people so I get this nice little silver medal next to my name. *The next step is a gold medal, but I must help 500 people. *Hope this helps! *If so, let me know, click "YES" below. -- Cheers, Ryan "Dan" wrote: Hi Ryan, My workbook will calc, then show the UserForm, then calc again...and again. * I have to manually get rid of the UserForm (however, Excel continues to calc). *There are a few worksheets in the workbook with formulas, so I'm not sure if that makes a difference. I really can't send you the file as it contains confidential info. *Unless you have any more suggestions, I guess that I'll have to throw in the towel on this one. *I'll check back just in case, but if you want to move onto other posts, then I'll say thanks for all of your time. * I'm also not sure if you get points when people hit the 'yes', but I will as a thanks for your time & effort. Thanks again, Dan "Ryan H" wrote: Excel must be calculating before it shows the userform. *I'm not sure if we can stop that. *Give this a try. *This set of code will turn off calculation mode, show your userform, calculate, then unload the userform (in theory). Private Sub Worksheet_Calculate() * * Application.Calculation = xlCalculationManual * * UserForm1.Show End Sub Private Sub UserForm_Initialize() * * Application.Calculate * * Do * * * * If Application.CalculationState = xlDone Then * * * * * * Application.Calculation = xlCalculationAutomatic * * * * * * Unload UserForm1 * * * * * * Exit Do * * * * End If * * Loop End Sub If this doesn't work send me an e-mail to with your workbook and I will take a look at it. *I may be able to turn off calculation mode to the workbook and take advantage of some pivot tabel events. *Hope this helps! *If so, let me know, click "YES" below. -- Cheers, Ryan "Dan" wrote: Hi Ryan, Thanks again for all of your help with this - I do have a follow-up though: The macro that you wrote worked great except that it only fires once calculation is complete (so the window pops-up just after calculation stops). *My goal is to get the window to pop-up once calculation starts and then go away after it stops so the user doesn't start clicking on the worksheet and interrupt the calc process. *Is there some way to start the userform when calculation state = xlpending or xlcalculating? and then have it stop once calculation is complete? Again - I appreciate all of your time! Thanks, Dan "Ryan H" wrote: You should be able to accomplish what you are looking for using a Userform. * I will assume you just have a Label on the userform that says, "Excel is still calculating...Please Wait." or something like that. *Since I can't duplicate your pivot table situation I was not able to test this code. *So let me know if you have any issues. *Please give specifics of the errors if any. *Let me know if this helps, click "YES" below. Put this in the worksheet that is being calculated. *This code will show the userform when the worksheet beings to calculate. Private Sub Worksheet_Calculate() * * UserForm1.Show End Sub Put this in the userform module. *This code will fire when the userform is shown. *It will continue the loop until Excel is done calculating. Private Sub UserForm_Activate() * * Do * * * * If Application.CalculationState = xlDone Then * * * * * * Unload UserForm1 * * * * * * Exit Do * * * * End If * * Loop End Sub -- Cheers, Ryan "Dan" wrote: Hi Ryan, Thanks for the reply - I appreciate it. *The reason behind my original question is a pretty sizable model that I have built around a couple of data sources that link to a data warehouse (downloaded into Pivot and data tables within the model). *The model takes raw data from numerous sources and combines it into a simple user-friendly analytical format that the user can manipulate via simple drop-downs. *Given the amount of data involved, a couple of the user selections on the Dashboard tab require about 10-20 seconds for Excel to calculate. *However, users are sometimes too impatient and will start clicking on the worksheet if the calc is not instantaneous, which will interrupt the calculation process. *Hence, my desire to have a window pop-up that informs the user that calculation is going on and to be patient (the Excel status bar in the lower-right hand corner is not obvious enough). *I have discovered the following VBA code (see below) that initiates a pop-up window upon calculation, however, I cannot get the window to disappear once calculation stops (have tried a few IF THEN statements, but in vain) - the only way to get rid of it with the code below is to manually close it, which i do not want the user to have to do... Private Sub Worksheet_Calculate() * * msg = "Calculating...Please Wait" * * MsgBox msg * * Do * * Loop Until Application.CalculationState = xlDone ' *End Sub Is there a way to get the message box to automatically go away once calculation has stopped? Also, I have created a UserForm (UserForm1) to replace the message box (so I can format it), however, i cannot get the message to show-up in the user form, only the UserForm itself - can you help? Thanks in advance! Dan "Ryan H" wrote: Excel should not take but a second to calculate everything. *I'm not sure why you want to do what you are wanting to do. *Do you have a macro that is taking a long time to run? *If so, here is a tip to speed up your macro. Sub YourMacro() * * With Application * * * * .ScreenUpdating = False * * * * .Calculation = xlCalculationManual * * * * .StatusBar = "Excel is still calculating...Please Wait." * * End With * * ' your code here * * With Application * * * * .ScreenUpdating = True * * * * .Calculation = xlCalculationAutomatic * * * * .StatusBar = False * * End With End Sub Note: You can't easily use a MsgBox to tell the user Excel is still calculating. *But you can change the text in the Status Bar in the lower left corner in Excel. Hope this helps! *If so, let me know, click "YES" below. -- Cheers, Ryan "Dan" wrote: Hi - I am new to VBA and am trying to figure out how to get a message box to pop-up whenever Excel calculates so the user waits and does not interrupt the calculation process. *Specifically, it would remain on the screen until the calculation process stops. Thanks!- Hide quoted text - - Show quoted text - |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA message box to inform user that Excel is still calculating
Application.EnableCancelKey = xlDisabled
oh yes, a glorious day. I stumbled upon this and it works! On Feb 22, 8:46*am, Jeffrey Marcus wrote: Hi, I am having a similar issue. I want to prevent the user from interrumpting the "application.calculate" command. Auto calc is off to start, in the button, I set it to manual and save the current calc mode state. *Throughout the subroutnie in the button I execute application.calcualte several times and they can take a few minutes. *The issues that if the user click the sheet during this application.calculate the application stops the calculate and continues to the next statement. Dan, *Did you ever settle on a good fix for this? Thanks, Jeff On Feb 18, 3:24*pm, Ryan H wrote: Since I can't see your situation I am left to guessing. *Give this situation a try. 1. *Set Calculate Mode to Manual for the Workbook. 2. *Put this code in the worksheet module the pivot table is located. *I assume the name of your pivot table is "PivotTable1". *This code will fire each time the pivot table is updated. Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) * * If Target.Name = "PivotTable1" Then UserForm1.Show End Sub 3. *Put this code in the Userform module. *The code will now manually calculate your workbooks. *And Excel will test if it is still calculating every 1 second. *If its not, it should unload the userform. Private Sub UserForm_Initialize() * * Application.Calculate * * Call WaitTimer End Sub Private Sub WaitTimer() * * If Application.CalculationState < xlDone Then * * * * Application.OnTime EarliestTime:=Now + TimeValue("00:00:01"), _ * * * * * * * * * * * * * * Procedu="WaitTimer", Schedule:=True * * Else * * * * Unload UserForm1 * * End If End Sub 4. *And yes, I guess you could say we get points. *I have helped over 100 people so I get this nice little silver medal next to my name. *The next step is a gold medal, but I must help 500 people. *Hope this helps! *If so, let me know, click "YES" below. -- Cheers, Ryan "Dan" wrote: Hi Ryan, My workbook will calc, then show the UserForm, then calc again...and again. * I have to manually get rid of the UserForm (however, Excel continues to calc). *There are a few worksheets in the workbook with formulas, so I'm not sure if that makes a difference. I really can't send you the file as it contains confidential info. *Unless you have any more suggestions, I guess that I'll have to throw in the towel on this one. *I'll check back just in case, but if you want to move onto other posts, then I'll say thanks for all of your time. * I'm also not sure if you get points when people hit the 'yes', but I will as a thanks for your time & effort. Thanks again, Dan "Ryan H" wrote: Excel must be calculating before it shows the userform. *I'm not sure if we can stop that. *Give this a try. *This set of code will turn off calculation mode, show your userform, calculate, then unload the userform (in theory). Private Sub Worksheet_Calculate() * * Application.Calculation = xlCalculationManual * * UserForm1.Show End Sub Private Sub UserForm_Initialize() * * Application.Calculate * * Do * * * * If Application.CalculationState = xlDone Then * * * * * * Application.Calculation = xlCalculationAutomatic * * * * * * Unload UserForm1 * * * * * * Exit Do * * * * End If * * Loop End Sub If this doesn't work send me an e-mail to with your workbook and I will take a look at it. *I may be able to turn off calculation mode to the workbook and take advantage of some pivot tabel events. *Hope this helps! *If so, let me know, click "YES" below. -- Cheers, Ryan "Dan" wrote: Hi Ryan, Thanks again for all of your help with this - I do have a follow-up though: The macro that you wrote worked great except that it only fires once calculation is complete (so the window pops-up just after calculation stops). *My goal is to get the window to pop-up once calculation starts and then go away after it stops so the user doesn't start clicking on the worksheet and interrupt the calc process. *Is there some way to start the userform when calculation state = xlpending or xlcalculating? and then have it stop once calculation is complete? Again - I appreciate all of your time! Thanks, Dan "Ryan H" wrote: You should be able to accomplish what you are looking for using a Userform. * I will assume you just have a Label on the userform that says, "Excel is still calculating...Please Wait." or something like that. *Since I can't duplicate your pivot table situation I was not able to test this code. *So let me know if you have any issues. *Please give specifics of the errors if any. *Let me know if this helps, click "YES" below. Put this in the worksheet that is being calculated. *This code will show the userform when the worksheet beings to calculate. Private Sub Worksheet_Calculate() * * UserForm1.Show End Sub Put this in the userform module. *This code will fire when the userform is shown. *It will continue the loop until Excel is done calculating. Private Sub UserForm_Activate() * * Do * * * * If Application.CalculationState = xlDone Then * * * * * * Unload UserForm1 * * * * * * Exit Do * * * * End If * * Loop End Sub -- Cheers, Ryan "Dan" wrote: Hi Ryan, Thanks for the reply - I appreciate it. *The reason behind my original question is a pretty sizable model that I have built around a couple of data sources that link to a data warehouse (downloaded into Pivot and data tables within the model). *The model takes raw data from numerous sources and combines it into a simple user-friendly analytical format that the user can manipulate via simple drop-downs. *Given the amount of data involved, a couple of the user selections on the Dashboard tab require about 10-20 seconds for Excel to calculate. *However, users are sometimes too impatient and will start clicking on the worksheet if the calc is not instantaneous, which will interrupt the calculation process. *Hence, my desire to have a window pop-up that informs the user that calculation is going on and to be patient (the Excel status bar in the lower-right hand corner is not obvious enough). *I have discovered the following VBA code (see below) that initiates a pop-up window upon calculation, however, I cannot get the window to disappear once calculation stops (have tried a few IF THEN statements, but in vain) - the only way to get rid of it with the code below is to manually close it, which i do not want the user to have to do... Private Sub Worksheet_Calculate() * * msg = "Calculating...Please Wait" * * MsgBox msg * * Do * * Loop Until Application.CalculationState = xlDone ' *End Sub Is there a way to get the message box to automatically go away once calculation has stopped? Also, I have created a UserForm (UserForm1) to replace the message box (so I can format it), however, i cannot get the message to show-up in the user form, only the UserForm itself - can you help? Thanks in advance! Dan "Ryan H" wrote: Excel should not take but a second to calculate everything. *I'm not sure why you want to do what you are wanting to do. *Do you have a macro that is taking a long time to run? *If so, here is a tip to speed up your macro. Sub YourMacro() * * With Application * * * * .ScreenUpdating = False * * * * .Calculation = xlCalculationManual * * * * .StatusBar = "Excel is still calculating....Please Wait." * * End With * * ' your code here * * With Application * * * * .ScreenUpdating = True * * * * .Calculation = xlCalculationAutomatic * * * * .StatusBar = False * * End With End Sub Note: You can't easily use a MsgBox to tell the user Excel is still calculating. *But you can change the text in the Status Bar in the lower left corner in Excel. Hope this helps! *If so, let me know, click "YES" below. -- Cheers, Ryan "Dan" wrote: Hi - I am new to VBA and am trying to figure out how to get a message box to pop-up whenever Excel calculates so the user waits and does not interrupt the calculation process. *Specifically, it would remain on the screen until the calculation process stops. Thanks!- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Relacing default Excel message box when user attempts to change locked cell on protected sheet. | Excel Programming | |||
How do I insert a message when a user opens my Excel workbook? | Excel Discussion (Misc queries) | |||
Displaying a message in a message box without requiring user to click anything to proceed | Excel Programming | |||
How to find a specific inform with some cells and return total | Excel Programming | |||
Status Message User Form in Excel '97 | Excel Programming |