Something like this maybe
Userform
Option Explicit
Private Sub cbLogExit_Click()
With Worksheets("Log")
.Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = Format(Now, "yyyy-mm-dd hh:mm:ss")
'use (0,1) in case previous comment was blank
.Cells(.Rows.Count, 1).End(xlUp).Offset(0, 1).Value = Me.tbComments.Text
End With
bCancel = False
Me.Hide
Unload Me
End Sub
Private Sub cbReturn_Click()
bCancel = True
Me.Hide
Unload Me
End Sub
Thisworkbook
Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Not ThisWorkbook.Saved Then
Load UserForm1
UserForm1.Show
Cancel = bCancel
End If
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Load UserForm1
UserForm1.Show
Cancel = bCancel
End Sub
Standard module
Option Explicit
Public bCancel As Boolean