VBA to handle errors if opening source file was not successful

Question

I modified codes from the youtube tutorial (https://www.youtube.com/watch?v=h_sC6Uwtwxk, thank you) to import data from other excel files.

Sub get_data_from_source_file()

Dim FileToOpen As Variant
Dim SrcWB As Workbook
Dim SrcWS As Worksheet
Dim SrcRng As String
Let SrcRng = "A2:I501"
Dim DesWB As Workbook
Dim DesWS As Worksheet
Set DesWS = Worksheets("MAIN")
Dim DesLR As Long
DesLR = Application.WorksheetFunction.CountA(DesWS.Range("A1:A50001"))
Dim DesRng As String  
Let DesRng = "A" & DesLR + 1 & ":" & "I" & DesLR + 500
Dim sFileName As String

Application.ScreenUpdating = False

FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", filefilter:="Excel Files (*.xlsx;.*.csv*),*xlsx;csv*")
If FileToOpen <> False Then
On Error GoTo WrongPWD
Set SrcWB = Application.Workbooks.Open(FileToOpen)
ThisWorkbook.Worksheets("MAIN").Range(DesRng) = SrcWB.Sheets(1).Range(SrcRng).Value
SrcWB.Close False
MsgBox "Data import was successful."
End If

WrongPWD:

        If Err.Number = 1004 Then

        Dim MsgPrompt As String
        MsgPrompt = "The file could not be opened. Try again?"
        Select Case MsgBox(prompt:=MsgPrompt, Buttons:=vbYesNoCancel, Title:="Decision")
        Case Is = vbYes: 'Do nothing and let the code loop
        Case Is = vbNo: Exit Sub
        Case Else: Exit Sub 'User canceled (includes VbCancel and pressing the x top right corner)
        End Select


        Exit Sub

        End If

Application.ScreenUpdating = True

End Sub

The code works fine. My source files, however, may somehow be password-protected. When I enter incorrect password the code terminates. Please enlighten me for codes to handle this error, like prompting a msgbox, notifying user to run command again and exit sub, etc?

Answer 1

Here is an example of how you could do it:

Dim wb As Workbook

Do While wb Is Nothing
    On Error Resume Next
        Set wb = Workbooks.Open(FilePath)
    On Error GoTo 0
    If wb Is Nothing Then
        Dim MsgPrompt As String
        MsgPrompt = "The file could not be opened. Try again?"
        Select Case MsgBox(prompt:=MsgPrompt, Buttons:=vbYesNoCancel, Title:="Decision")
            Case Is = vbYes: 'Do nothing and let the code loop
            Case Is = vbNo: Exit Sub
            Case Else: Exit Sub 'User canceled (includes VbCancel and pressing the x top right corner)
        End Select
    End If
Loop