Where data downloaded from a database exceeds a combo box’s range, the new items are simply not shown. To counter this, the range that underlies List or Combo boxes needs to expand or contract to match the data. This article examines how to do this automatically.
It is assumed the reader has the Developer ribbon displayed and is familiar with the VBA Editor. If not, please Google “Excel Developer Tab” or “Excel Code Window”.
A professional way of displaying combo boxes is to have their ranges expand or contract as needed. For example:
The key to a dynamic range is monitoring the number of populated rows in the relevant column, using the function =countA. This function counts populated elements in a sequence of cells until it hits the last one; in the case of the first diagram in the image above, that would be row 11.
To maintain a range automatically requires defined names to track the number of populated rows. For example we use eCol (end column) and eRow (end row) to define the boundaries of our range. The more rows we populate, the greater eRow’s value becomes.
The defined names above have set the list to one column wide (eCol = 1) by the number of populated rows in eCol (eRow = 11)
Finally, the Titles appear in the range “A2:A” & eRow. You will note the Index function is used to establish the last cell in the range called “Titles”. Effectively the range “A2:A” & eRow translates to “A2:A11” at this stage.
We can set up the dynamic range automatically when the workbook opens, by using the Auto_open sub procedure, which runs before the workbook becomes visible.
Open a workbook and populate it with a combo box and some data. The sample workbook used in this exercise can be found here.
Open the VBA code window and insert a module. Copy the code below into the module.
The Auto_Open event sets up the last row and last column values for the dynamic range “Titles”, and notes subsequent changes made.
Sub auto_open() Dim eRow As Integer, eCol As Integer, i As Long On Error Resume Next 'Clear the present define names, to avoid any duplications activeworkbook.Names("eCol").Delete activeworkbook.Names("eRow").Delete activeworkbook.Names("Titles").Delete Range("A1").Select 'Titles will appear in the first column, A in this case eCol = 1 'Find the last populated row eRow = Sheets("Main").Cells(Rows.Count, eCol).End(xlUp).Row 'Define the names activeworkbook.Names.Add Name:="eCol", RefersTo:="=COUNTA($1:$1)" activeworkbook.Names.Add Name:="eRow", RefersToR1C1:="=COUNTA(C" & ColNo & ")" activeworkbook.Names.Add Name:="Titles", RefersTo:="=A2:INDEX($2:$200," & "eRow," & "eCol)" End Sub Sub DropDown1_Change() MsgBox "Directed by " & Cells(2, 5) End Sub
Note: Everything has been placed on a single page to simplify viewing. Typically the values in columns A & B, and D & E would be on another, possibly hidden, sheet. Please also note that the second column, B, is not used in the definitions of the range in this particular exercise; column B’s values are obtained in the usual way via the combo box’s cell-link property (D2 reflects the selection of the third element in the range, which starts at A2, coupled with an Index function in “E3” to find the director ( =INDEX(B:B,D2+1,1)).
Save the workbook, and then re-open it. The combo box will initially be populated by Auto_open. Add items to column A and B, and observe the changes to the combo box.
Salvage Damaged Excel Files
From time to time, Excel files may get corrupted after Excel crashes unexpectedly. If you have a backup, then you can simply restore the data with your backup. Otherwise, you may need to seek a professional expert or tool to recover the corrupt Excel files.
Felix Hooker is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including rar repair and sql recovery software products. For more information visit www.datanumen.com
Leave a Reply