VBA code to remind the Excel sheet user to run the Macro

[SamFares]

Hello,

I have created a macro for design in an excel sheet that has two tabs in it.  What is the VBA code if a user make any input changes to remind him in the end before he exists to run the design macro? Instead of selecting certain cells for changes, is there a way to make it any change in either tab?

Thanks,

Sam 

[#2]

@SamFares

The following will work if the workbook only prompts to be saved when it is closed if the user has changed something.

Press Alt+F11 to activate the Visual Basic Editor.

Double-click ThisWorkbook under Microsoft Excel Objects.

Copy the following code into the ThisWorkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Not Me.Saved Then
        Select Case MsgBox("Do you want to run the design macro now?", vbQuestion + vbYesNoCancel)
            Case vbYes
                Call DesignMacro
            Case vbNo
                ' Just continue
            Case vbCancel
                Cancel = True
        End Select
    End If
End Sub

where DesignMacro is the name of the macro that should be run.

[#3]

I will try it. Thank you so much Hans!

[#4]

Hello Hans,
I tried it but I am not sure what I am doing wrong. The design Macro is called
"Calculate_Studs_CP". So if a user make any changes in "CJ Design" or "Addl Composite Stage Loads" tabs, and the user wants to save or exit to prompt him to run "Calculate_Studs_CP". Please see attached sheet.
Thanks,
Sam

[#5 · Accepted solution]

@SamFares 

You have copied the code into the code module of the CJ Design and Addl Composite Stage Loads sheets instead of into the ThisWorkbook module.

[#6]

Thank you Hans!

[#7]

Hello Hans,
If I make changes in the Excel sheet and I "Save" the sheet before exiting then the reminder macro to run the DesignMacro message doesn't pop on the screen. It works fine if I try to exit without saving. How do you account for that condition so the message window shows up when i save or exit.

Thanks,
Sam

[#8]

@SamFares 

Asking both when the user closes the workbook and when it is saved is bound to be confusing.

See if you can live with the following:

Delete the Workbook_BeforeClose event procedure from the ThisWorkbook module.

Add the following code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Select Case MsgBox("Do you want to run the design macro now?", vbQuestion + vbYesNoCancel)
        Case vbYes
            Call DesignMacro
        Case vbNo
            ' Just continue
        Case vbCancel
            Cancel = True
    End Select
End Sub

[#9]

Thanks a lot Hans. I like it because it works for conditions.

Sam