Error Handler....yes no buttons

Davers

Well-known Member
Joined
Sep 17, 2002
Messages
1,165
Hi everyone...I've created a bit o' code that saves a file using cell references to set the name. I'd like an error handler on it that uses the Yes/No buttons so I can replace the file if I want by pressing yes. Currently my code looks like the below:

Code:
Dim myFile As String
Dim myDir As String
Dim myErr As String
Dim fs As New Scripting.FileSystemObject

myErr = Range("D7").Value
myDir = Range("D6").Value
On Error GoTo HandleErr

fs.CreateFolder myDir
myFile = Worksheets("Main").Range("D8").Value
    ThisWorkbook.SaveAs myFile
    
HandleErr:

If Err.Number = 58 Then
    MsgBox Err.Description & " - " & myErr & " - " & "Would you like to replace the exisiting file?", vbYesNo
Else
    Resume Next
End If

End Sub

I don't know how to set the yes button on the MsgBox so it will replace the file with I hit the button, and not replace the file when I hit the no button...

Any ideas?! :rolleyes:

Thanks,

Dave (y)
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
Hi Dave,

You need to test for the user's response. Here's two options, pick which suits:
Code:
Sub Test()
    Dim iAnswer As Integer
    
    iAnswer = MsgBox(prompt:="What's it gonna be?", Title:="Well?", Buttons:=vbYesNo)
    If iAnswer = vbYes Then
        MsgBox "Yes"
    Else
        MsgBox "No"
    End If
    
End Sub

Sub Test2()
    
    If MsgBox(prompt:="What's it gonna be?", Title:="Well?", Buttons:=vbYesNo) = vbYes Then
        MsgBox "Yes"
    Else
        MsgBox "No"
    End If
    
End Sub
Also, remember to add an Exit Sub before your error-handler to avoid it always running at the end of the routine :wink:

HTH
 

Davers

Well-known Member
Joined
Sep 17, 2002
Messages
1,165
Cool, thanks for the response!!! I appreciate it!

Have a good day,

Dave M.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,323
Messages
5,769,454
Members
425,549
Latest member
Bartekelese

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top