VBA: Ejecutar macro antes de cerrar Libro: Workbook_BeforeClose.

En el post de hoy explicaré un evento de bastante utilidad como es Workbook_BeforeClose, en respuesta a un mail de un lector del blog:

...Desearia saber si me pueden ayudar con una macro que uso para cerrar una hoja de Excel y al salir grabar la hoja. La macro es la siguiente: Sub Botón2_Haga_clic_en() ActiveWorkbook.SaveAs "c:\Facturas\" & Range("AI18").Value Application.Quit End Sub La celda AI18 es el numero de la factura, pero quisiera que al cerrar y grabar la hoja también me grabara el nombre de el cliente además del numero de factura....

Emplearé nuestro evento, que se ejecuta antes de cerrar nuestros libros de trabajo, sabiendo que si nuestro libro ha sido modificado, este evento se produce antes de solicitar al usuario que guarde dichas modificaciones. Esto es, que cualquier modificación no quedaría salvada.
Por este motivo añadiremos un código bien sencillo que fuerza, ante cualquier modificación sufrida en nuestro libro de trabajo, una ventana de 'Guardar como'.
Veamos entonces el código a incluir en el Editor de VBA (Alt+F11), dentro de Thisworbook del explorador de proyectos es:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'ruta o path donde queremos guardar el fichero

ruta = "D:\Mis documentos\"

'definimos el nombre del fichero en función a dos celdas de la Hoja1

nombrefile = Sheets(1).Range("A1").Value & "_" & Sheets(1).Range("B1").Value

rutacompleta = ruta + nombrefile

If Not Me.Saved Then

'considero que ha habido alguna modificación desde la última vez que se guardó

pregunta = MsgBox("¿Deseas guardar los cambios realizados en " & rutacompleta, _

vbQuestion + vbYesNoCancel)

Select Case pregunta

Case vbYes

ActiveWorkbook.SaveAs Filename:=rutacompleta, _

FileFormat:=xlOpenXMLWorkbookMacroEnabled

Case vbNo

Me.Saved = True

Case vbCancel

Cancel = True

Exit Sub

End Select

End If

End Sub

El resultado al cerrar nuestro libro de trabajo, si hemos realizado algún cambio en él, es que se abre una ventana diálogo como esta:

haz click en la imagen

cerrándose directamente, sin pedir confirmación de guardado, si no se hubiera producido cambio alguno.