Need help with Array with type, invalid qualifier & runtime error

[Mr. GetRight]

Ok. Not sure what I am doing wrong so i will start off with what I want to do.
Below is my code that is to go out to location of workbook, look for other
workbooks saved in subfolders,save their location, then manager's name, and
year from the file name to an array. ex location: C:\Documents and
Settings\Guest\Desktop\cand be deleted\2008 CBT Tracker for DoeJane.xls

Problems:
1 I am getting an complier error for line starting off
"ListbManagerNameSelection". It is for the array. I was trying to make it
global because I will reuse them on another part of the workbook.
I removed that part out to make sure the rest would work but when it
attempts to save data to the first array i receive run time error 424. what
am i doing wrong?

Private Sub UserForm_Activate()

If (NumFiles = LoadFileDate(CBTfilesArray)) = True Then ' Goes to This
Workbook code screen and loads manger name, year, file path\name

For count = 1 To NumFiles
ListbManagerNameSelection.List = CBTfilesArray.ManagerName
'Output manager's name in list box to be selected by user
Next count

Else

MsgBox "There were no files found."

End If

End Sub



'Code below is saved in Module
Option Explicit
Public CBTfilesArray() As Variant
Type CBTfilesArray
ManagerName As String
CBTtrackingYear As Integer
CBTtrackerFileName As String
End Type
Function LoadFileDate()
Dim fs As Object
Dim i As Integer
Dim Mypath As String



Mypath = ActiveWorkbook.Path
Set fs = Application.FileSearch
MsgBox Mypath

With fs
'Change below to give the name of the Directory you want to search
.LookIn = Mypath & "\"
.SearchSubFolders = True
.FileName = "*.xls"

If .Execute() > 0 Then

For i = 1 To .FoundFiles.count

ReDim Preserve CBTfilesArray(i - 1)

CBTfilesArray(i - 1).ManagerName = Mid(.FoundFiles(i),
InStrRev(.FoundFiles(i), " ") + 1) 'Get Manager Name and adds it to array
CBTfilesArray(i - 1).CBTtrackingYear = (Mid(.FoundFiles(i),
InStrRev(.FoundFiles(i), "\") + 1, 4)) ' Get the year of the CTB Tracker
CBTfilesArray(i - 1).FileName = (.FoundFiles(i)) 'Get file
address and addes to array


Next i

LoadFileDate = .FoundFiles.count
Else
LoadFileDate = False
End If

End With

If LoadFileDate Then
MsgBox (LoadFileDate + 1) & " files found. Managers are " &
CBTfilesArray(1).ManagerName
Else
MsgBox "No files were loaded into Array"
End If
End Function

 

[Joel]

You need to put count as the index to the array in the code below

For Count = 1 To NumFiles
ListbManagerNameSelection.List = _
CBTfilesArray(Count).ManagerName
'Output manager's name in list box
'to be selected by user
Next Count

 

[Mr. GetRight]

I tried it but it still says "Invalid Qualifier" for that line. Also, on my
org code, that line should have just been:
ListbManagerNameSelection.List = CBTfilesArray.ManagerName
I forgot to delete the For loop from before. per book, the above code will
input array in list without having to loop. But either way i still get the
Invalid Qualifier Error when running it.

 

[Joel]

I HATE EXCEL!!!!!

This is another quirk of excel. Excel lets you fill a list box using anthe
array method when you use an array like

Listbox1.list = Array(1,2,3,4,5)

but doesn't allow you to fill it with a variable declared as an array

Dim MyList() as Variant

MyList(0) = 1
MyList(1) = 2
MyList(2) = 3
MyList(3) = 4

Listbox1.list = MyList ' this doesn't work

Instead

for Count = 0 to ubound(CBTfilesArray)

ListbManagerNameSelection.additem _
CBTfilesArray(Count).ManagerName
next Count

 

[Mr. GetRight]

I know what you mean, only reason I'm using it is because no one else knows
how to use Access and they like Excel. I tryied that but it is still saying
Invalid Qualifer on CBTfilesArray.

 

[Joel]

Try this test code

Public MyArray(10) As CBTfilesArray
Type CBTfilesArray
ManagerName As String
CBTtrackingYear As Integer
CBTtrackerFileName As String
End Type
Sub test()

MyArray(0).ManagerName = 1

For Count = 0 To UBound(MyArray)

ListbManagerNameSelection.AddItem _
MyArray(Count).ManagerName
Next Count

End Sub

 

[Mr. GetRight]

Thanks! Joel, we did it! What i wasn't doing was "Public MyArray(10) As
CBTfilesArray" I also had to go back and change some of the code that ref to
CBTfilesArray to MyArray. There were some other coding issues but it it
running now. Thanks again.