VBA Run time error '5'

12Rev79

New Member
Joined
Mar 2, 2021
Messages
35
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Dear Experts,

I have the code below that would list all the files in selected directory.

It work so far the error happens when I click Cancel.

Can anyone is kind to help me fix the error?

1618233396688.png


VBA Code:
Private Sub OldName_Click()

Dim fldr As FileDialog
Dim sItem As String
Dim strPath As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    .InitialFileName = strPath
    If .Show <> -1 Then GoTo NextCode
    sItem = .SelectedItems(1)
End With
NextCode:
Set fldr = Nothing
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim mysub As Object
Dim i As Integer


Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(sItem)
i = 1
For Each objFile In objFolder.Files
Cells(i + 2, 2) = objFile.Name
Cells(i + 2, 4) = objFile.Path
i = i + 1
Next objFile

For Each mysub In objFolder.SubFolders
Cells(i + 2, 2) = mysub.Name
Cells(i + 2, 4) = mysub.Path
i = i + 1

Next mysub
End Sub

Thank you in advance.
12Rev79
 
Last edited by a moderator:

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,207
Office Version
  1. 365
Platform
  1. Windows
Try changing this
VBA Code:
If .Show <> -1 Then GoTo NextCode
to
VBA Code:
If .Show <> -1 Then Exit Sub
 
Solution

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
915
Office Version
  1. 2010
Platform
  1. Windows
Hi,​
as a reminder to just list files from a folder better use the VBA Dir function rather than the external FSO, that's easier & faster !​
Another point : at 99% a good VBA procedure does not need any Goto statement …​
 

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
857
Office Version
  1. 365
Platform
  1. Windows
Never ever ever never use GoTo. When you press Cancel, the code keeps going anyway and sItem is null when you reach the statement causing the error.

I see that @Fluff has provided one viable solution. I was going to rewrite the logic to avoid an Exit but that is the quickest fix.
 

12Rev79

New Member
Joined
Mar 2, 2021
Messages
35
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

Try changing this
VBA Code:
If .Show <> -1 Then GoTo NextCode
to
VBA Code:
If .Show <> -1 Then Exit Sub
Thank you so much Fluff it work well, appreciated.
 

12Rev79

New Member
Joined
Mar 2, 2021
Messages
35
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hi,​
as a reminder to just list files from a folder better use the VBA Dir function rather than the external FSO, that's easier & faster !​
Another point : at 99% a good VBA procedure does not need any Goto statement …​
Thank you Marc L, I will take that kind information
 

12Rev79

New Member
Joined
Mar 2, 2021
Messages
35
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Never ever ever never use GoTo. When you press Cancel, the code keeps going anyway and sItem is null when you reach the statement causing the error.

I see that @Fluff has provided one viable solution. I was going to rewrite the logic to avoid an Exit but that is the quickest fix.
Thank you 6StringJazzer
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,207
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,802
Messages
5,655,379
Members
418,193
Latest member
RichKatzRich

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