I have a file to track sales prospecting with 400 rows. Each row has 2 combo boxes (ActiveX) which track seperate metrics. Hence, I have 800 combo boxes total.
Each combo box is in a cell, and is Linked to the same cell so it dumps the choice from the combo box into that cell. This way Worksheet 1 will populate with the row of data from rows which have the selection of Option 1 from the combo box.
The file size is 3MB. To open this file takes anywhere from 2-3 minutes to open (tested around 6 times and this was average) - ridiculous and unacceptable for my purposes.
Through process of elimination I deleted every single combo box and re-saved (this was the only change). Then it took only 15-25 seconds to open on average.
My question is this - I want to force people's selection via combo box to track selections and limit their choice to 5 options for both combo boxes. But with 800 combo boxes total, the whole file is bogged down for whatever reason (maybe because it has to sync all of the linked cell to combo boxes?... not sure).
Any idea how I can improve the efficiency of this file? I'd like to use combo boxes because they are very simple and force a fixed selection of 1/5 options.
PLEASE HELP! Thanks in advance for any/all comments.
P.S. - there is no confidential information in my file so if you are a guru and would like to help me out, PM me your email address and I can send it your way. I would be forever thankful!
Bookmarks