SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-2395908%22%20slang%3D%22en-US%22%3EVBA%20code%20to%20remind%20the%20Excel%20sheet%20user%20to%20run%20the%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2395908%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20created%20a%20macro%20for%20design%20in%20an%20excel%20sheet%20that%20has%20two%20tabs%20in%20it.%26nbsp%3B%20What%20is%20the%20VBA%20code%20if%20a%20user%20make%20any%20input%20changes%20to%20remind%20him%20in%20the%20end%20before%20he%20exists%20to%20run%20the%20design%20macro%3F%20Instead%20of%20selecting%20certain%20cells%20for%20changes%2C%20is%20there%20a%20way%20to%20make%20it%20any%20change%20in%20either%20tab%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3ESam%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2395908%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2395938%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20code%20to%20remind%20the%20Excel%20sheet%20user%20to%20run%20the%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2395938%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F351459%22%20target%3D%22_blank%22%3E%40SamFares%3C%2FA%3E%3C%2FP%3E%0A%3CP%3EThe%20following%20will%20work%20if%20the%20workbook%20only%20prompts%20to%20be%20saved%20when%20it%20is%20closed%20if%20the%20user%20has%20changed%20something.%3C%2FP%3E%0A%3CP%3EPress%20Alt%2BF11%20to%20activate%20the%20Visual%20Basic%20Editor.%3C%2FP%3E%0A%3CP%3EDouble-click%20ThisWorkbook%20under%20Microsoft%20Excel%20Objects.%3C%2FP%3E%0A%3CP%3ECopy%20the%20following%20code%20into%20the%20ThisWorkbook%20module%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EPrivate%20Sub%20Workbook_BeforeClose(Cancel%20As%20Boolean)%0A%20%20%20%20If%20Not%20Me.Saved%20Then%0A%20%20%20%20%20%20%20%20Select%20Case%20MsgBox(%22Do%20you%20want%20to%20run%20the%20design%20macro%20now%3F%22%2C%20vbQuestion%20%2B%20vbYesNoCancel)%0A%20%20%20%20%20%20%20%20%20%20%20%20Case%20vbYes%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Call%20DesignMacro%0A%20%20%20%20%20%20%20%20%20%20%20%20Case%20vbNo%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20'%20Just%20continue%0A%20%20%20%20%20%20%20%20%20%20%20%20Case%20vbCancel%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Cancel%20%3D%20True%0A%20%20%20%20%20%20%20%20End%20Select%0A%20%20%20%20End%20If%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ewhere%20DesignMacro%20is%20the%20name%20of%20the%20macro%20that%20should%20be%20run.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2395940%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20code%20to%20remind%20the%20Excel%20sheet%20user%20to%20run%20the%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2395940%22%20slang%3D%22en-US%22%3EI%20will%20try%20it.%20Thank%20you%20so%20much%20Hans!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2396162%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20code%20to%20remind%20the%20Excel%20sheet%20user%20to%20run%20the%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2396162%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Hans%2C%3CBR%20%2F%3EI%20tried%20it%20but%20I%20am%20not%20sure%20what%20I%20am%20doing%20wrong.%20The%20design%20Macro%20is%20called%3CBR%20%2F%3E%22Calculate_Studs_CP%22.%20So%20if%20a%20user%20make%20any%20changes%20in%20%22CJ%20Design%22%20or%20%22Addl%20Composite%20Stage%20Loads%22%20tabs%2C%20and%20the%20user%20wants%20to%20save%20or%20exit%20to%20prompt%20him%20to%20run%20%22Calculate_Studs_CP%22.%20Please%20see%20attached%20sheet.%3CBR%20%2F%3EThanks%2C%3CBR%20%2F%3ESam%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

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 

8 Replies

@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.

I will try it. Thank you so much Hans!

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

best response confirmed by SamFares (Contributor)
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.

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

@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
Thanks a lot Hans. I like it because it works for conditions.

Sam