Passing Variables Through A UserForm Using Public Variable Declaration

idlewyld89

New Member
Joined
Jun 10, 2018
Messages
23
Hi all, hopefully a relatively straightforward question. The following code is what I'm testing:

Userform code:

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:
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?
 
Last edited:

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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)
 
Upvote 0
Another option (assuming the file is not open)
Code:
Set DirectoryWorkbook = Workbooks.Open(fd.SelectedItems.Item(1))
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

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
Back
Top