Forum Discussion
macro trouble: wait, msgbox and manual calc
Hello. I cant figure out why this macro fails. When I make a change to cells u30:u400 I want a 60 second pause, pop up a message, then I want the excel sheet to revert to manual mode calculation. What am I doing wrong? Two things are not happening: I dont get a MsgBox and Excel remains in AutoCalculation mode.
Private Sub AdditionalSharesWait(ByVal Target As Range)
Dim KeyCells As Range
' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("U30:U400")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
' Display a message when one of the designated cells has been
' changed.
' Place your code here.
MsgBox "Cell " & Target.Address & " has changed."
Application.Wait (Now + TimeValue("00:01:00"))
End If
ManualCalculate
End Sub
My manual calc macro is confirmed good:
Sub ManualCalculate()
'
' ManualCalculate Macro
'
'
Application.Calculation = xlManual
End Sub
Thanks hope you can help
4 Replies
Hello Greg Bonaparte,
May be you can try this :
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("U30:U400")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then' Display a message when one of the designated cells has been
' changed.
' Place your code here.
MsgBox "Cell " & Target.Address & " has changed."
Application.Wait (Now + TimeValue("00:01:00"))Application.Calculation = xlCalculationManual
End If
End sub
how do you trigger the macro? did you use an "on change" event on the page where you want the macro run?
No I did not use " change" but i just tried that and fixed problem. Thank you.
I did not get the desired result though. I wanted to edit the spreadsheet while the timer was running. Instead however the spreadsheet hour glasses (preventing additional edit) then reverts to manual calc. I dont want manual calc mode until i finish editing. It would also be nice to see the timer countdown. Any advice?I do not know why you need to stop calculation until finishind editing.
However you can try to create a user form for data entry for the data you need. Than after "OK" button pressed you can write those values to the required cells. This would be the easiest solution. You can also force this user form to be shown whenever the workbook opens or someone try to change the cells where user is only allowed by this user form.
If you want to display time passed only, you can try to load a user form in non modal state to show the timer however it will be sent back whenever Excel is activated. I do not know if it is possible to keep it on top while active, never needed that functionality.
