Forum Discussion
VBA code to remind the Excel sheet user to run the Macro
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
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.
8 Replies
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
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.
- I will try it. Thank you so much Hans!
