Message Box to Notify of Update

[Banker1]

I have a workbook that takes a decent amount of time to recalculate based on a series of very complex formulas. I'd like to add a message box triggered by a worksheet_change event (already written) which pops a message box noting the sheet is in the process of updating.

I don't want the user to have to interact with the message box to get the in order to have the macro finish it's cycle.

Any suggestions?

 

[VoG]

Maybe

Do While Application.CalculationState <> "Done"
    Application.StatusBar = "Updating"
    DoEvents
Loop
Application.StatusBar = False

 

[Banker1]

No luck... here's the code (inc. your suggestion)

Private Sub Worksheet_Change(ByVal Target As Range)

Do While Application.CalculationState <> "Done"

Application.StatusBar = "Updating"

Call ChangeEvent1(Target)
Call ChangeEvent2(Target)

Loop

Application.StatusBar = False

End Sub

' Updates dropdown list fiters based on selected product

Private Sub ChangeEvent1(ByVal Target As Range)

If Intersect(Target, Range("Returns_Report_DivisionName_Select")) Is Nothing Then
    Exit Sub
Else
    Range("Returns_Report_MarketingChannel_Select").Value = "All"
    Range("Returns_Report_Keycode").Value = "All"
    Range("Returns_Report_ConversionChannel").Value = "All"
End If

End Sub

'Updates dropdown list filters based on selected product and marketing channel

Private Sub ChangeEvent2(ByVal Target As Range)

If Intersect(Target, Range("Returns_Report_MarketingChannel_Select")) Is Nothing Then
    Exit Sub
Else
    Range("Returns_Report_Keycode").Value = "All"
    Range("Returns_Report_ConversionChannel").Value = "All"
End If

End Sub

 

[VoG]

You can't do it like that. All of the code has to go in the Worksheet_Change event. You cannot make up your own event procedures.

 

[Banker1]

Interesting... how would I modify the change event to incorporate multiple change scenarios then? Based on my research the only way to get multiple change events to trigger correctly I needed to create a separate event and call it in the main change event.

Hope that makes sense.