User form x button

[Greg]

Hi all

I have tried to get excel to disable the "x" button I have tries the
following code but it does not do what i want.

Private Sub UserForm_QueryClose _
(Cancel As Integer, CloseMode As Integer)
Dim wb As Workbook
'Prevents use of the Close button
If CloseMode = vbFormControlMenu Then
MsgBox "ARE YOU SURE YOU WISH TO QUIT? DO YOU WANT TO SAVE FIRST?",
vbYesNoCancel
If vbYes Then

For Each wb In Workbooks
wb.Save
Next wb
Application.Quit
End If
If vbNo Then
applcation.quit
End If
If vbCancel Then
Cancel = True

End If

End If
End Sub
What I am looking to do it have the message box ask the question, if yes is
pressed to save the workbook and close the application.
If no is pressed to quit the application and if cancel is pressed to return
to normal.

Thanks

Greg

 

[Andy Pope]

Hi,

You need to store the result of the msgbox response and process accordingly.

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Dim wb As Workbook
Dim lngStatus As VbMsgBoxResult

'Prevents use of the Close button
If CloseMode = vbFormControlMenu Then
lngStatus = _
MsgBox("ARE YOU SURE YOU WISH TO QUIT? DO YOU WANT TO SAVE FIRST?", _
vbYesNoCancel)
If vbYes Then
For Each wb In Workbooks
wb.Save
Next wb
Application.Quit
ElseIf vbNo Then
Application.Quit
Else
Cancel = True
End If
End If

End Sub

Cheers
Andy

 

[Ardus Petus]

MsgBox is a function that returns an integer that you must test against vb
constants.

Private Sub UserForm_QueryClose _
(Cancel As Integer, CloseMode As Integer)
Dim wb As Workbook
'Prevents use of the Close button
If CloseMode = vbFormControlMenu Then
Select Case MsgBox( _
"ARE YOU SURE YOU WISH TO QUIT? DO YOU WANT TO SAVE FIRST?", _
vbYesNoCancel)

Case vbYes
For Each wb In Workbooks
wb.Save
Next wb
Application.Quit

Case vbNo
applcation.Quit

Case vbCancel
Cancel = True
End Select
End If
End Sub


HTH

 

[Andy Pope]

Doh! Helps if I finish the code...

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Dim wb As Workbook
Dim lngStatus As VbMsgBoxResult

'Prevents use of the Close button
If CloseMode = vbFormControlMenu Then
lngStatus = _
MsgBox("ARE YOU SURE YOU WISH TO QUIT? DO YOU WANT TO SAVE FIRST?", _
vbYesNoCancel)
If lngStatus = vbYes Then
For Each wb In Workbooks
wb.Save
Next wb
Application.Quit
ElseIf lngStatus = vbNo Then
Application.Quit
Else
Cancel = True
End If
End If

End Sub

 

[Greg]

Thanks for that

Greg

Doh! Helps if I finish the code...

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Dim wb As Workbook
Dim lngStatus As VbMsgBoxResult

'Prevents use of the Close button
If CloseMode = vbFormControlMenu Then
lngStatus = _
MsgBox("ARE YOU SURE YOU WISH TO QUIT? DO YOU WANT TO SAVE FIRST?", _
vbYesNoCancel)
If lngStatus = vbYes Then
For Each wb In Workbooks
wb.Save
Next wb
Application.Quit
ElseIf lngStatus = vbNo Then
Application.Quit
Else
Cancel = True
End If
End If

End Sub