Calculation popup not working

[Hoube78]

Hi,

I have an excel dashboard which has a large data set and when a user changes certain options the calculations can take between 2 to 4 seconds to update.

I tried to create a message that popup when the user changes asking them to wait, see below code:

msg = "Calculating...Please Wait"
MsgBox msg
Do
Loop Until Application.CalculationState = xlDone

I tried the above on the combo box change event, the sheet calculation, the combo box click.

However the issue I have is that the message does not pop up until the calculations are complete which was not the purpose.

I looked at a userform option but can only find code for macro or sql query which does not fit my issue?

Any ideas on how to resolve or any other options?

Thanks in advance to all that looks or helps out

John

 

[Drrellik]

The few MSG box's I have used are in userforms and need the user to click ok for the msg box to exit unto the routine to finish, so I am not sure how to have a MSG box popup and stay while calculations are done then close. which is what you want.

 

[Hoube78]

I just want something to pop up and tell the user that excel is calculating and to wait and close when excel has finished calculating.

At the moment the popup works but after the calculations have finished and not before

The few MSG box's I have used are in userforms and need the user to click ok for the msg box to exit unto the routine to finish, so I am not sure how to have a MSG box popup and stay while calculations are done then close. which is what you want.

 

[Drrellik]

are the calculation done in VBA

you can add instructions to the sheet that tell your customers that the calculations will take a few seconds and to not close and save until you get your message.

then put you complete msgbox at the end of the vba

saying done close and save or what ever.

 

[Hoube78]

Hi,

No at the moment calculations are being done automatically via excel.

Calculations are updated based on a users choice via a combo box e.g user chooses Round 1 from drop down which then updates a table for date sumifs in the background (user has two of these to choose).

Based on what your saying below I think your saying I should turn calculations to manual in the workbook and then have a VBA e.g. cmd button which activates the calculation once the user has chosen their options?

I have thought about this and I think the VBA is the only way to go as I now have 3 Dashboards which have this issue now (I know its wrong but its what my client wants is to have the 7 dashboards in one workbook which is why the calculations is now an issue plus the data set being so large with lots of sumifs)

are the calculation done in VBA

you can add instructions to the sheet that tell your customers that the calculations will take a few seconds and to not close and save until you get your message.

then put you complete msgbox at the end of the vba

saying done close and save or what ever.

 

[Drrellik]

http://www.mrexcel.com/forum/genera...3-visual-basic-applications-msgbox-timer.html

This is a thread here on the site and here is some code I found on line very similar

Sub MessageBoxTimer()
    Dim AckTime As Integer, InfoBox As Object
    Set InfoBox = CreateObject("WScript.Shell")
    'Set the message box to close after 5 seconds
    AckTime = 5
    Select Case InfoBox.Popup("Please wait while calculations are being done.", _
    AckTime, "Claculation Being Done", 0)
        Case 1, -1
            Exit Sub
        End Select

End Sub

 

[Hoube78]

Hi Thanks,

Guessing I use this code in a button and then put the calculations code under it or call a separate sub routine to do calculations?

Thank you for your help on this more work than I thought so will have to do some testing to see how stop calculations impacts the workbook first.

Thanks again
John