Excel VBA - On Error Execute Code

Question

Im using Application.FileDialog(msoFileDialogFolderPicker) to choose a folder. This is executed by using a button in userform. However before the user choose the folder a new sheet will be created. Then the open file dialog [Application.FileDialog(msoFileDialogFolderPicker)] will pop up.

Function SelectFolder(Optional msg As String) As String
Dim diaFolder As FileDialog

Set diaFolder = Application.FileDialog(msoFileDialogFolderPicker)
diaFolder.AllowMultiSelect = False
diaFolder.Title = msg
diaFolder.Show
On Error Resume Next
SelectFolder = diaFolder.SelectedItems(1)
On Error GoTo 0
Set diaFolder = Nothing
End Function

The problem arises when user decided to cancel to choose the folder. When this happen, the newly created sheet should be deleted. I tried to use error handler but now luck.

ErrorHandler:
If SheetExists("MS") Then Application.Run "DeleteSheet.deleteSh1"
If SheetExists("MS2") Then Application.Run "DeleteSheet.deleteSh2"
If SheetExists("MT") Then Application.Run "DeleteSheet.deleteSh3"

Application.Run "HideSheets.hideSh"
Resume Next
Exit Sub

Hope you guys can give some idea on this.

I would first pop the SelectFolder window up, and depending on the answer I would create the sheet - or not. If in this case it doesn't work, instead of On Error GoTo 0 you should use On Error GoTo ErrorHandler (GoTo 0 Disables enabled error handler in the current procedure and resets it to Nothing. - [link]msdn.microsoft.com/en-us/library/5hsw66as.aspx ) Or if your ErrorHandler is in a separate Sub, call it from the error section of your SelectFolder Function — tretom
– tretom, Commented Dec 22, 2016 at 10:28
@tretom i tried to include the errorhandler in the function but seems not working. i guess something missing. thats why i asked here. — MG78
– MG78, Commented Dec 23, 2016 at 10:13

brettdj · Accepted Answer · 2016-12-22 10:23:41Z

1

why not create the sheet when you have a valid response?

That said, you could check the length of the string you are looking for - 0 means cancel, i.e.

Dim strResponse As String
strResponse = SelectFolder("test")
If Len(strResponse) = 0 Then
  MsgBox "user cancelled", vbCritical
  'delete sheet
End If

answered Dec 22, 2016 at 10:23

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

A.S.H · Accepted Answer · 2016-12-22 10:26:06Z

0

I imagine the above routine SelectFolder is called somehow from a sub that first creates a worksheet then calls it. You could achieve your goal like this:

Sub MyButton_Click()
    Dim newWS as worksheet, folder as String
    set newWS = sheets.Add
    folder = SelectFolder("please select your folder")

    If folder = "" Then
       newWS.Delete
    Else
       ' ... proceed with the selected folder
    End If
End Sub

However, creating the Worksheet before getting the user's answer doesn't seem to me like a good approach, unless there's some strong reason.

answered Dec 22, 2016 at 10:26

A.S.H

29.4k6 gold badges25 silver badges50 bronze badges

5 Comments

A.S.H Over a year ago

@brettdj Nope, the raised error is handled inside the SelectFolder function provided in the OP.

brettdj Over a year ago

actually, yes it will. I missed you handled it the same way i had in my earlier answer - thought you had written a new standalone sub. my bad.

MG78 Over a year ago

@A.S.H i tried ur code. it is working but the expected result is not as i expected. i included my code into the ...proceed with the slected folder part...but seems only half working

A.S.H Over a year ago

@Jeeva What is the problem you are facing?

MG78 Over a year ago

Hi @A.S.H sorry for late reply. just back from Christmas Holiday. Anyway my mistakes. The part that not working is mine actually and i managed to find solution for it. You solution working fine now. Thank you!!!

Collectives™ on Stack Overflow

Excel VBA - On Error Execute Code

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related