Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why does excel load more slowly since I switched to McAfee antivi | Excel Discussion (Misc queries) | |||
How to load a listbox in a userform? | Excel Discussion (Misc queries) | |||
UserForm Wont Load | Excel Programming | |||
new error when try to load userform | Excel Programming | |||
Load a Userform | Excel Programming |