|
User form x button
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 |
User form x button
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 Greg wrote: 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, Microsoft MVP - Excel http://www.andypope.info |
User form x button
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 -- AP "Greg" a écrit dans le message de news: ... 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 |
User form x button
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 Andy Pope wrote: 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 Greg wrote: 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, Microsoft MVP - Excel http://www.andypope.info |
User form x button
Thanks for that
Greg "Andy Pope" wrote in message ... 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 Andy Pope wrote: 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 Greg wrote: 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, Microsoft MVP - Excel http://www.andypope.info |
| All times are GMT +1. The time now is 08:37 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com