Passing Variables Through A UserForm Using Public Variable Declaration

[idlewyld89]

Hi all, hopefully a relatively straightforward question. The following code is what I'm testing:

Userform code:

Private Sub DirectoryButton_Click()
'
'
'
Dim fd As FileDialog
Dim DataPath As String

Set fd = Application.FileDialog(msoFileDialogFilePicker)
    fd.Title = "Select Your Directory"
    fd.InitialView = msoFileDialogViewSmallIcons
    fd.Filters.Clear
    fd.Filters.Add "Excel Files", "*.xlsx;*.xls;*.xlsm"
    fd.ButtonName = "Set Reference"
    fd.Show
    
DataPath = Right$(fd.SelectedItems.Item(1), Len(fd.SelectedItems.Item(1)) - InStrRev(fd.SelectedItems.Item(1), "\"))
DirectoryText.Value = DataPath

Set DirectoryWorkbook = Workbooks(DataPath)

End Sub

Module code:

Option Explicit
Public DirectoryWorkbook As Workbook
Public DirectoryWorksheet As Worksheet
Public DirectoryTable As ListObject

Public DataWB As Workbook
Public DataWS As Worksheet

Dim MainSheet As String
Dim LastRow As Long

Sub NoREV_tool()
'
'
'

Set DirectoryWorkbook = Nothing
Set DirectoryWorksheet = Nothing
Set DirectoryTable = Nothing

NoREV_userform.Show

End Sub

For some reason, I keep getting an object out of range error on the line within the UserForm code that sets DirectoryWorkbook = Workbooks(DataPath)

DataPath ends up output as "GM Directory.xlsm"

Thoughts?

 

[ParamRay]

Hello,

This line is going to cause an error if DataPath isn't already open:
Set DirectoryWorkbook = Workbooks(DataPath)

So you might change it to this instead:
Set DirectoryWorkbook = Workbooks.Open(DataPath)

 

[Fluff]

Another option (assuming the file is not open)

Set DirectoryWorkbook = Workbooks.Open(fd.SelectedItems.Item(1))