Trying to open a file using a listbox selection. My code says quote "Compile error: User-defined type not defined" on the first line?

[Darren Smith]

Private Sub ListBox3_Click()

Dim objFSO As Scripting.FileSystemObject
Dim objFolder As Scripting.folder

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("\\TGS-SRV01\Share\ShopFloor\PRODUCTION\DLS Carworker")

Dim I As Long
For I = 0 To ListBox3.ListCount - 1
If ListBox3.Selected(I) Then
Workbook.Open objFolder & ListBox3.List(I)

End If
Next I

End Sub

 

[Trevor G]

Have you set the reference to use Microsoft Scripting Runtime through the Tools Menu.

 

[Darren Smith]

Have you set the reference to use Microsoft Scripting Runtime through the Tools Menu.

Have you set the reference to use Microsoft Scripting Runtime through the Tools Menu.

I`ve now set Scripting Runtime right but I find it says it can`t find the path to the spreadsheet. I`ve copied the path to the code from the file path it`s self???

 

[Trevor G]

Try placing a back slash at the end of the network location
DLS Carworker\

 

[Darren Smith]

I`ve done as you said but still not working

 

[Trevor G]

A couple of things?

1. What code are you using to fill the listbox?
2. Open one of the workbooks manually and in the VBA screen add a sub routine to identify the path, make sure the immediate window is open in the VBA screen, then run this code and what does it show you:
3. In the listbox are you showing the file extensions?

Sub Macro1()
Debug.Print ActiveWorkbook.Path

End Sub

 

[Darren Smith]

A couple of things?

1. What code are you using to fill the listbox?

Private Sub Userform_Initialize()
With ListBox3
.AddItem "Divider Color"
.AddItem "1 Page Job Card Master"
.AddItem "2 Page Job Card Master"
.AddItem "3 Page Job Card Master"
.AddItem "4 Page Job Card Master"
.AddItem "5 Page Job Card Master"
.AddItem "Open Inventory"
.AddItem "Open Job Record"
.AddItem "Fill Details"
.AddItem "Open Drawings"
.AddItem "Reset Conditional Formats"
.AddItem "PrintMultiple"

End With

End Sub

1. Open one of the workbooks manually and in the VBA screen add a sub routine to identify the path, make sure the immediate window is open in the VBA screen, then run this code and what does it show you:

This line of code below is where it stops with an error. I tried to add debug print but it didn`t work
Set objFolder = objFSO.GetFolder("\\TGS-SRV01\Share\ShopFloor\PRODUCTION\DLS Carworker\")

1. In the listbox are you showing the file extensions? No just name of workbook

 

[Trevor G]

The code would need to identify its a workbook so it needs to know the file extension. So I'm going to suggest you try the following:

In Design view of the userform open the code screen and use the UserForm_Initialize and add this code (Change the directory path):

Private Sub UserForm_Initialize()
Dim fso As FileSystemObject
 Dim fld As Folder
 Set fso = New FileSystemObject
 Set fld = fso.GetFolder("put your path in here\") 'Change this path
 For Each Fil In fld.Files
 If UCase(Right(Fil.Name, 4)) = "XLSM" Then
 ListBox1.AddItem Fil.Name
 End If
 Next
End Sub

This should add the file names into the listbox. Run the form to see if it does. If yes then you can use this code to open the files, again change the path, and hopefully you will have a solution. I have tested both parts and it works, I have used listbox1 please change to your listbox name listbox3.

Private Sub ListBox1_Click()

 Workbooks.Open "put your path in here\" & ListBox1.Value
 UserForm1.Hide
End Sub

 

[Darren Smith]

Thanks, it works I would like the rest of the items down below if possible.

 

[Trevor G]

You haven't asked for anything else. I have answered you question and provided a solution for you.