May 28 2021 07:11 AM
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
May 28 2021 07:28 AM
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.
May 28 2021 07:30 AM
May 28 2021 08:43 AM - edited May 28 2021 09:44 AM
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
May 28 2021 08:56 AM
SolutionYou have copied the code into the code module of the CJ Design and Addl Composite Stage Loads sheets instead of into the ThisWorkbook module.
Jun 01 2021 07:00 AM - edited Jun 01 2021 07:02 AM
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
Jun 01 2021 07:13 AM
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
Jun 01 2021 07:35 AM
May 28 2021 08:56 AM
SolutionYou have copied the code into the code module of the CJ Design and Addl Composite Stage Loads sheets instead of into the ThisWorkbook module.