PDA

View Full Version : Solved: Error handling problem



Spratt_Cary
01-23-2006, 07:13 PM
:hi: I have a SAVE command button on my form: Here's the code.

Private Sub cmdSave_Click()
Const FileSave1 = "Do you want to save this IVR Bid?"
Const FileSave2 = "Saved in \\...Shared Area.................."
Const FileSave3 = "Sponsor Name_Study Number is blank. Click OK to type the Sponsor Name_Study Number"
Dim Response As String
Dim sPath As String

sPath = "\\pd-dept\pd-dept\Folder\Folder\IVR Bid UserForm\"
Response = MsgBox(FileSave1, vbYesNo + vbQuestion, "Save IVR Bid Algorithm?")

If Response = vbNo Then
Application.DisplayAlerts = False
Application.Quit
Else
If Range("B2").Value = "" Then
MsgBox FileSave3, vbOKOnly + vbInformation, "Enter Sponsor Name_Study Number"
Else
ActiveWorkbook.SaveAs Filename:=sPath & Range("B2").Value & Format(Now(), "-mm.dd.yyyy") & ".xls"
MsgBox FileSave2, vbOKOnly + vbInformation, "File Saved"
UserForm2.Hide
End If
End If
End Sub

My Problem is, if the enduser is not mapped to the specified path, then error 1004 "debug" pops up. I have tried several ways to put an error handler in the code, but I guess I am not understanding exactly how to write the code and then I mess up my command button. What I would like to see is, if the end user is not mapped to the shared area when saving the spreadsheet, a message would appear (Instead of the MS error message) stating that the spreadsheet was not saved and to please map to the correct drive "\\pd-dept\pd-dept\Folder\Folder\IVR Bid\". Can anyone help me. Thanks in advance from a novice.

Killian
01-23-2006, 07:47 PM
Hi and welcome to VBAX :hi:

One option would be to check the save path is available first, but to use error handling instead, you can use "OnError GoTo" before the save line and trap the error out the bottom of the routine to display the message and Exit Sub before the trap to get out if no error occursPrivate Sub CommandButton2_Click()
Const FileSave1 = "Do you want to save this IVR Bid?"
Const FileSave2 = "Saved in \\...Shared Area.................."
Const FileSave3 = "Sponsor Name_Study Number is blank. Click OK to type the Sponsor Name_Study Number"
Const FileSave4 = "Please map to the correct drive '\\pd-dept\pd-dept\Folder\Folder\IVR Bid\'"
Dim Response As String
Dim sPath As String

sPath = "\\pd-dept\pd-dept\Folder\Folder\IVR Bid UserForm\"
Response = MsgBox(FileSave1, vbYesNo + vbQuestion, "Save IVR Bid Algorithm?")

If Response = vbNo Then
Application.DisplayAlerts = False
Application.Quit
Else
If Range("B2").Value = "" Then
MsgBox FileSave3, vbOKOnly + vbInformation, "Enter Sponsor Name_Study Number"
Else
On Error GoTo errortrap
ActiveWorkbook.SaveAs Filename:=sPath & Range("B2").Value & Format(Now(), "-mm.dd.yyyy") & ".xls"
MsgBox FileSave2, vbOKOnly + vbInformation, "File Saved"
UserForm2.Hide
End If
End If
Exit Sub
errortrap:
UserForm2.Hide
MsgBox FileSave4, vbOKOnly + vbExclamation, "Error saving file!"
End Sub

Spratt_Cary
01-23-2006, 08:35 PM
Perfect, thank you, thank you, thank you

Zack Barresse
01-23-2006, 09:02 PM
Hello Spratt_Cary, I have marked this as Solved for you. In the future, you can mark your own threads as Solved by going to the Thread Tools | Mark Thread as Solved | Perform Action.

Take care! :)