Excel ActiveX combo boxes are slowing file open/save
Posted on 2014-03-19
I am having open/save issues with an Excel XLS formatted workbook.
The workbook starts as 2 worksheets. There no issues at this stage.
The first worksheet contains 2 named ranges, each with 20 items.
The second worksheet contains a lightly formatted form which extends to F23. It also contains 2 columns of 15 (a total of 30 per sheet) ActiveX Combo Boxes which reference the 2 named ranges. The second worksheet is copied within the workbook for each “component” of a particular job. Some jobs require 60 components, thus the second worksheet might be copied (under scripted control) up to 60 times within one workbook. With 60 sheets, the file size remains under 2Mb, containing 1800 (30x60) ActiveX controls.
With that many sheets, the workbook takes 20 seconds or more to load. While that’s not the end of the world, it triggers unpredictable user responses. It just seems wrong on an otherwise fast system.
I first tested by removing all ActiveX Combo Boxes, after which the workbook opened instantaneously. I then substituted all the ActiveX Combo Box controls for Excel Combo Box form controls, after which the workbook also opened instantaneously. Unfortunately the form control version of the combo box does not provide the programmability I require.
Note that anywhere from 1 to 30 of the combo boxes might be selected on any one sheet.
Considering there are only 2 unique combo boxes throughout the entire workbook, how can I make the form more efficient?