Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
additem performance
I have a combobox that is being populated from the contents of a
different worksheet in the same workbook. The problem is that it is taking over 10 seconds to run - it only runs once during the workbook_open event. Here is the code: ================================================== ======== Private Sub GeoComboBoxInit(GeoCombo As ComboBox) Dim i As Integer Dim ws As Worksheet Dim wb As Workbook Set wb = Workbooks(1) Set ws = wb.Sheets("GeoTier") For i = 2 To 24183 GeoCombo.AddItem ws.Cells(i, 3) Next i End Sub ================================================== ============= In a future release I will get the data from a local MS Access database. How can I speed up the current loading of this combobox? TIA mark markm-at-visionsw.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
additem performance
Try getting the data into a variant first
Private Sub GeoComboBoxInit(GeoCombo As ComboBox) Dim i As long Dim vArr as variant vArr= Workbooks(1).Sheets("GeoTier").range("C2:C24183") For i = 2 To 24183 GeoCombo.AddItem vArr(i-1,1) Next i End Sub -- Charles ______________________ Decision Models FastExcel 2.1 now available www.DecisionModels.com ================================================== ============= "Mark" wrote in message om... I have a combobox that is being populated from the contents of a different worksheet in the same workbook. The problem is that it is taking over 10 seconds to run - it only runs once during the workbook_open event. Here is the code: ================================================== ======== Private Sub GeoComboBoxInit(GeoCombo As ComboBox) Dim i As Integer Dim ws As Worksheet Dim wb As Workbook Set wb = Workbooks(1) Set ws = wb.Sheets("GeoTier") For i = 2 To 24183 GeoCombo.AddItem ws.Cells(i, 3) Next i End Sub ================================================== ============= In a future release I will get the data from a local MS Access database. How can I speed up the current loading of this combobox? TIA mark markm-at-visionsw.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
additem performance
Another possibility is to just assign it in one step:
Private Sub GeoComboBoxInit(GeoCombo As ComboBox) Dim i As long Dim vArr as variant GeoCombo.List = Workbooks(1).Sheets("GeoTier") _ .range("C2:C24183").Value End Sub -- Regards, Tom Ogilvy "Charles Williams" wrote in message ... Try getting the data into a variant first Private Sub GeoComboBoxInit(GeoCombo As ComboBox) Dim i As long Dim vArr as variant vArr= Workbooks(1).Sheets("GeoTier").range("C2:C24183") For i = 2 To 24183 GeoCombo.AddItem vArr(i-1,1) Next i End Sub -- Charles ______________________ Decision Models FastExcel 2.1 now available www.DecisionModels.com ================================================== ============= "Mark" wrote in message om... I have a combobox that is being populated from the contents of a different worksheet in the same workbook. The problem is that it is taking over 10 seconds to run - it only runs once during the workbook_open event. Here is the code: ================================================== ======== Private Sub GeoComboBoxInit(GeoCombo As ComboBox) Dim i As Integer Dim ws As Worksheet Dim wb As Workbook Set wb = Workbooks(1) Set ws = wb.Sheets("GeoTier") For i = 2 To 24183 GeoCombo.AddItem ws.Cells(i, 3) Next i End Sub ================================================== ============= In a future release I will get the data from a local MS Access database. How can I speed up the current loading of this combobox? TIA mark markm-at-visionsw.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with: ListBox1.AddItem (ws.Name) | Excel Worksheet Functions | |||
AddItem Method | Excel Programming | |||
Please help with AddItem method | Excel Programming | |||
.additem | Excel Programming | |||
additem listbox | Excel Programming |