Forum Discussion
VBA code to remind the Excel sheet user to run the Macro
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.
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 Subwhere DesignMacro is the name of the macro that should be run.
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,
SamAsking 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- Thanks a lot Hans. I like it because it works for conditions.
Sam
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,
SamYou have copied the code into the code module of the CJ Design and Addl Composite Stage Loads sheets instead of into the ThisWorkbook module.
- Thank you Hans!
- I will try it. Thank you so much Hans!
