I have a spreadsheet that users can interact with to specify the file path to 4 different files needed to be opened to run some macros. The code includes a check to see if the file path they have entered is valid or not (works excellently). However, what I want to do is have a message box appear if anything doesn't work and then also tell the user which one didn't work.
My code does do that perfectly (albeit in I think a quite convoluted way) however as the array is set to have 4 values it means if the final file isn't present, it starts the text 4 lines down in the message box instead of at the top.
What I want to do, I believe, is ReDim the array to only the amount of files missing so that the MsgBox isn't 3 empty lines below the first sentence. I've kinda figured that bit out but I just could not get it working properly and now I am stumped.
Sub Open_month_0()
On Error GoTo ErrHand
ThisWorkbook.ActiveSheet.Calculate
Dim i As String
Dim j As String
Dim k As String
Dim l As String
Dim m As String
Dim n As String
Dim o As String
Dim p As String
Dim arr(4) As Variant
Dim File_Missing As Integer
'Used as a counter to prompt either an error or successful result
File_Missing = 0
i = Range("LUX_Full_file_path")
j = Range("LUX_Full_file_name")
k = Range("JUP_Full_file_path_M")
l = Range("JUP_Full_file_name_M")
m = Range("JUP_Full_file_path_Q")
n = Range("JUP_Full_file_name_Q")
o = Range("JUP_Full_file_path_A")
p = Range("JUP_Full_file_name_A")
'The if not's check to see if the file path is valid. If it isn't, gets added to array and File_missing begins
If Not Dir(i, vbDirectory) = vbNullString Then
Workbooks.Open (i)
Windows(j).Visible = False
Else
arr(1) = "Lux file"
File_Missing = File_Missing + 1
End If
If Not Dir(k, vbDirectory) = vbNullString Then
Workbooks.Open (k)
Windows(l).Visible = False
Else
arr(2) = "Monthly file"
File_Missing = File_Missing + 1
End If
If Not Dir(m, vbDirectory) = vbNullString Then
Workbooks.Open (m)
Windows(n).Visible = False
Else
arr(3) = "Quarterly file"
File_Missing = File_Missing + 1
End If
If Not Dir(o, vbDirectory) = vbNullString Then
Workbooks.Open (o)
Windows(p).Visible = False
Else
arr(4) = "Annual file"
File_Missing = File_Missing + 1
End If
'Basic error handling procedure that retains function.
If File_Missing > 0 Then
MsgBox ("The following files could not be found. Please check the file paths and try again" & vbCrLf & Join(arr, vbCrLf))
Else
MsgBox "Files opened successfully."
End If
Exit Sub
ErrHand: MsgBox "There has been a critical error with opening the chosen workbooks. If the problem persists, please contact your administrator for assistance."
End Sub
Edit with pictures:


Redim Preserve arr(FileMissing)?