hi,

i want to run a macro whenever i decide to close excel sheet. is there a way how to do it?

this is my coding so far, and as i said, i want to run this macro whenever i close the worksheet.

Sub test()
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Please make sure you have updated the DATE!" ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "DATE" ' Define title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
        ' context.
        ' Display message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.
    MyString = "Yes" ' Perform some action.
    ThisWorkbook.Close
    
Else ' User chose No.
    MyString = "No" ' Perform some action.
End If
End Sub

1 open the VB Editor.

2 View the Project Explorer if not already visible.

3 Right click over the "ThisWorkbook" object. Select 'View code'.

4 In the Code page, add the following code...
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Please make sure you have updated the DATE!" ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "DATE" ' Define title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
        ' context.
        ' Display message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.
    MyString = "Yes" ' Perform some action.
    ThisWorkbook.Close

Else ' User chose No.
    MyString = "No" ' Perform some action.
End If
End Sub


Cheers

Matt