When a workbook is closed in Excel, there are a few ways to run a macro. One way is to use the Workbook_BeforeClose event. This event occurs when the workbook is about to be closed. We can use this event to run a macro that will save the workbook in a certain format or close it without saving. Another way to run a macro when a workbook is closed is to use the Workbook_Deactivate event. This event occurs when the workbook loses focus, which can happen when it's closed. We can use this event to run a macro that will save the workbook in a certain format or close it without saving.

To use either of these events, we first need to open the workbook that we want to run the macro on. Then, we go to the Visual Basic Editor (VBE) by pressing Alt+F11. In the VBE, we find the workbook in the Project Explorer window and double-click it. This will open the code window for the workbook. In the code window, we need to choose which event we want to use. For the Workbook_BeforeClose event, we type:

Private Sub Workbook_BeforeClose(Cancel As Boolean) 
End Sub

For the Workbook_Deactivate event, we type:

Private Sub Workbook_Deactivate() 
End Sub

Then, we write our macro code between the Sub and End Sub lines. For example, if we want to save the workbook as an .xlsx file when it's closed, we would use this code:

Private Sub Workbook_BeforeClose(Cancel As Boolean) 

    ThisWorkbook.SaveAs Filename:="C:\Users\username\Documents\workbook.xlsx", FileFormat:=xlOpenXMLWorkbook 

    Cancel = True 

    Application.Quit 

 End Sub 

 Private Sub Workbook_Deactivate() 

    ThisWorkbook.SaveAs Filename:="C:\Users\username\Documents\workbook.xlsx", FileFormat:=xlOpenXMLWorkbook 

    Application.Quit 

 End Sub