msgbox & inputbox


Posted by Cory on June 29, 2001 10:04 PM

I need to know the proper syntax for msgboxes and inputboxes. I've been using the following code to prompt a user to choose whether or not they'd like to save before exiting my form (and excel), or to cancel and return to the form. Could I improve it? Does it work?:

Private Sub CommandButton3_Click()
Dim Msg, Style, Title, Response
Msg = "Would you like to save this quote before exiting?"
Style = vbYesNoCancel + vbExclamation
Title = "Save This Quote?"
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
Dim Fs As String
Fs = Application.GetSaveAsFilename(fileFilter:="Microsoft Excel File (*.xls), *.xls")

If Fs = "False" Then Exit Sub
ThisWorkbook.SaveAs Fs
ActiveWorkbook.Close
ElseIf Response = vbCancel Then
Exit Sub
Else
ActiveWorkbook.Close
End If
End Sub

-----
Also, sometimes when I'm using msgboxes with three button options, such as vbYesNoCancel, regardless of which button is clicked all three conditions will run. For example, the code ahead would try and SaveAs... whether or you clicked Yes, No, or Cancel:


Private Sub CommandButton1_Click()
Dim Msg, Style, Title, Response
Msg = "Would you like to save before exiting? You haven't finished yet..."
Style = vbYesNoCancel + vbExclamation
Title = "Save This Quote?"
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
Dim Fs As String
Fs = Application.GetSaveAsFilename(fileFilter:="Microsoft Excel File (*.xls), *.xls")
If Fs = "False" Then Exit Sub
ThisWorkbook.SaveAs Fs
ActiveWorkbook.Close
ElseIf Response = vbCancel Then
Exit Sub
Else
ActiveWorkbook.Close
End If
End If
End Sub

Help? Please?
Cory

Posted by Brian Tetlow on June 30, 2001 4:24 PM

Hi Cory,

Try:

Private Sub CommandButton3_Click()
Response = MsgBox("Would you like to save this quote before exiting?", vbYesNoCancel + vbCritical + vbDefaultButton2)
If Response = vbCancel Then
Exit Sub
ElseIf Response = vbYes Then
Dim Fs As String
Fs = Application.GetSaveAsFilename(fileFilter:="Microsoft Excel File (*.xls), *.xls")
ElseIf Fs = "False" Then Exit Sub
ThisWorkbook.SaveAs Fs
ActiveWorkbook.Close
End If
End Sub



Posted by Brian Tetlow on June 30, 2001 4:32 PM

Sorry, that should have been:

Private Sub CommandButton3_Click()
Response = MsgBox("Would you like to save this quote before exiting?", vbYesNoCancel + vbCritical + vbDefaultButton2)
If Response = vbCancel Then
Exit Sub
ElseIf Response = vbNo Then
Exit Sub
ElseIf Response = vbYes Then
Dim Fs As String
Fs = Application.GetSaveAsFilename(fileFilter:="Microsoft Excel File (*.xls), *.xls")
If Fs = "False" Then
Exit Sub
Endif
ThisWorkbook.SaveAs Fs
ActiveWorkbook.Close
End If
End Sub