On Thu, 22 Feb 2007 21:47:10 -0500, "Tom Ogilvy"
wrote:

use the queryclose event.

http://support.microsoft.com/default...;en-us;Q166341
XL97: Preventing UserForm from Being Dismissed with Close Button (Q166341)



http://support.microsoft.com/default...;en-us;Q213713
XL2000: Preventing UserForm from Being Dismissed with Close Button (Q213713)





Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As _
Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
Msgbox "Can't close form this way. Use a button"
End if
End Sub

Thanks again Tom. A bit of pondering as I wanted both 'Save & Exit'
and 'Exit Without Saving'. Without the Forms Close button
circumventing either. Managed it thus:
--
Private Sub btnNoSave_Click()
mSave = 1
Unload Me
End Sub

Private Sub btnSave_Click()
mSave = 2
Unload Me
End Sub

Private Sub UserForm_Terminate()

If mSave < 1 Then
If mSave = 0 Then mSave = MsgBox("Save Changes", vbYesNo + 32, "This
Spreadsheet")

If mSave = 2 Or mSave = 6 Then

ActiveWorkbook.Save
End If
End If
End Sub
--

Maybe other ways but this seems to work. mSave was dimmed in the
declarations section and set to 0 in UserForm_Initialize.

Thanks - Kirk