I'm looking for a Macro to issue a warning when closing an excel workbook.

[Certificates!]

I have a workbook which I share with other team members. To ensure that they do not let things go awry for otehr colleagues I want a macro that issues a warning when the workbook is closed. I want the warning to read "Ensure the data matches V1 prior to closing this file." Is this possible and if so how.

I have little understanding of macros at all other than that they can be used to do lots of useful things.

Thank you

 

[Logit]

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Res As VbMsgBoxResult

    Res = MsgBox("Ensure the data matches V1 prior to closing this file." & vbNewLine & vbNewLine & "OK - CLOSE       CANCEL - OPEN", vbQuestion + vbOKCancel, "Check V1 Data")
    
    If Res = vbOK Then
        Cancel = False
        Exit Sub
    End If
    
    Cancel = True
   
End Sub

 

[Certificates!]

Thanks for this but I'm not sure what to do with it. I tried copying and pasting it into the View Code bit of the worksheet but there waasn't any warning offered when I closed the file. It's likely to be me rather than the code no doubt.

 

[Logit]

The macro is to be pasted into the ThisWorkbook Module.;

Download the sample workbook and open the VBA coding window. Easiest way is to right click any sheet tab and select VIEW CODE.
On the left side find ThisWorkbook module and double click that. The macro code will display in the right side view pane.

 

[Certificates!]

Thanks fro that. I really think I have done it, but it doesn't seem to work.

Can you see where I have gone wrong? I have pasted the code onto the worksheet where I thought it should go

 

[Logit]

Review the attached.

 

[Belleke]

You placed the code in the sheet module,you have to place it in the ThisWorkbook module as stated in post #4 (see screenshot)

 

[Certificates!]

That's perfect. Thanks very much to you both

 

[Logit]

Glad you have your answer.

 

[Belleke]

Glad to help. but all the credit goes to Logit.