Userform List Boxes Load Very Slowly
 

I have a userform with two listboxes that is taking too
long to show up on the screen. The listboxes are filled
with the filenames in a subdirectory. The code works, but
it's just way too slow. Can anyone tell me what's wrong
with the code? Here it is:

Private Sub UserForm_Initialize()

'Make a list of filenames

Dim Directory As String
Dim i As Integer
Dim Listnum As Integer
Dim Item As Variant

Directory = Range("Inputsubdirectory").Value

'Fill ListBox1 with portfolios
On Error Resume Next
With Application.FileSearch
.NewSearch
.LookIn = Directory
.Filename = "*.xls*"
.SearchSubFolders = False
.Execute

For i = 1 To .FoundFiles.Count
ListBox1.AddItem Dir(.FoundFiles(i)) 'Use
DIR to show only filename in listbox
Next i
End With

'Fill ListBox2 with default benchmark choices
With Application.FileSearch
.NewSearch
.LookIn = ThisWorkbook.Path & "\Benchmark Weights\"
.Filename = "*.xls*"
.SearchSubFolders = False
.Execute

For i = 1 To .FoundFiles.Count
ListBox2.AddItem Dir(.FoundFiles(i))
Next i
End With

'Pick S&P 500 as default benchmark to highlight in
ListBox2
Listnum = -1
For Each Item In ListBox2.list
Listnum = Listnum + 1
If Item = "S&P 500.xls" Then
ListBox2.SetFocus
ListBox2.ListIndex = Listnum
Exit For
End If
Next Item

End Sub

Userform List Boxes Load Very Slowly
 

Instead of using Dir to get the filename, use

Dim sName as String

sName = .Foundfile(i)
ListBox1.AddItem right(sName,len(sname)-Instrrev(sName,"\"))


sName = .Foundfile(i)
ListBox2.AddItem right(sName,len(sname)-Instrrev(sName,"\"))

This requires xl2000 or later since InstrRev was added in that version.

so it may not be an option for you.

I would also get the index number of the S&P entry in the loop to add the
items and skip the additional loop

If Instr(sName,"S&P 500.xls") 0 then
idex = Listbox2.Listcount - 1
End if

--
Regards,
Tom Ogilvy


"Stratuser" wrote in message
...
I have a userform with two listboxes that is taking too
long to show up on the screen. The listboxes are filled
with the filenames in a subdirectory. The code works, but
it's just way too slow. Can anyone tell me what's wrong
with the code? Here it is:

Private Sub UserForm_Initialize()

'Make a list of filenames

Dim Directory As String
Dim i As Integer
Dim Listnum As Integer
Dim Item As Variant

Directory = Range("Inputsubdirectory").Value

'Fill ListBox1 with portfolios
On Error Resume Next
With Application.FileSearch
.NewSearch
.LookIn = Directory
.Filename = "*.xls*"
.SearchSubFolders = False
.Execute

For i = 1 To .FoundFiles.Count
ListBox1.AddItem Dir(.FoundFiles(i)) 'Use
DIR to show only filename in listbox
Next i
End With

'Fill ListBox2 with default benchmark choices
With Application.FileSearch
.NewSearch
.LookIn = ThisWorkbook.Path & "\Benchmark Weights\"
.Filename = "*.xls*"
.SearchSubFolders = False
.Execute

For i = 1 To .FoundFiles.Count
ListBox2.AddItem Dir(.FoundFiles(i))
Next i
End With

'Pick S&P 500 as default benchmark to highlight in
ListBox2
Listnum = -1
For Each Item In ListBox2.list
Listnum = Listnum + 1
If Item = "S&P 500.xls" Then
ListBox2.SetFocus
ListBox2.ListIndex = Listnum
Exit For
End If
Next Item

End Sub

Userform List Boxes Load Very Slowly
 

Tom,

Thanks for your suggestions. I tried them, and they help
a little bit. But I found that a much bigger part of the
slowdown seems to be caused by the way I am assigning the
Lookin command to the network drive in the file path,
which is "\\SIGNAS\SIM\...". When I change the file path
to its literal mapped drive letter on my PC, which
is "J:\...", there is a huge pickup in speed in this
subroutine. But since my drive mapping could change, and
another user might have a different drive letter letter on
his PC, I'd like to leave the file reference as in terms
that the network will always understand. Any
suggestions?




-----Original Message-----
Instead of using Dir to get the filename, use

Dim sName as String

sName = .Foundfile(i)
ListBox1.AddItem right(sName,len(sname)-Instrrev
(sName,"\"))


sName = .Foundfile(i)
ListBox2.AddItem right(sName,len(sname)-Instrrev
(sName,"\"))

This requires xl2000 or later since InstrRev was added in
that version.

so it may not be an option for you.

I would also get the index number of the S&P entry in the
loop to add the
items and skip the additional loop

If Instr(sName,"S&P 500.xls") 0 then
idex = Listbox2.Listcount - 1
End if

--
Regards,
Tom Ogilvy


"Stratuser" wrote
in message
...
I have a userform with two listboxes that is taking too
long to show up on the screen. The listboxes are filled
with the filenames in a subdirectory. The code works,
but
it's just way too slow. Can anyone tell me what's wrong
with the code? Here it is:

Private Sub UserForm_Initialize()

'Make a list of filenames

Dim Directory As String
Dim i As Integer
Dim Listnum As Integer
Dim Item As Variant

Directory = Range("Inputsubdirectory").Value

'Fill ListBox1 with portfolios
On Error Resume Next
With Application.FileSearch
.NewSearch
.LookIn = Directory
.Filename = "*.xls*"
.SearchSubFolders = False
.Execute

For i = 1 To .FoundFiles.Count
ListBox1.AddItem Dir(.FoundFiles
(i)) 'Use
DIR to show only filename in listbox
Next i
End With

'Fill ListBox2 with default benchmark choices
With Application.FileSearch
.NewSearch
.LookIn = ThisWorkbook.Path & "\Benchmark
Weights\"
.Filename = "*.xls*"
.SearchSubFolders = False
.Execute

For i = 1 To .FoundFiles.Count
ListBox2.AddItem Dir(.FoundFiles(i))
Next i
End With

'Pick S&P 500 as default benchmark to highlight in
ListBox2
Listnum = -1
For Each Item In ListBox2.list
Listnum = Listnum + 1
If Item = "S&P 500.xls" Then
ListBox2.SetFocus
ListBox2.ListIndex = Listnum
Exit For
End If
Next Item

End Sub


.