Jun 1, 2007
I have a combobox that is not populating correctly. The design of it uses an existing list (which changes every month) to populate the contents of the combobox. I want the user to be able to select a location from the list if it is already on the worksheet, rather than having to key in the location name for every single record. This Sub worked just fine and dandy for me until I deployed the workbook to the end user. Then this particular combobox decided not to work.
View 9 Replies
I have been over this code many, many times and I am almost positive that it's just a matter of my not being able to see the obvious. Perhaps another set of eyes looking at the code will be able to see what I can not.
Dim allCells As Range, Cell As Range
Dim noDupes As New Collection
' get the first from last row #--the last row is a row of record counts and sums.
endRange = ActiveSheet.UsedRange.Rows.Count - 1
ComboBox1.Clear ' This will clear the combobox.
If endRange 65536 Then ' If the row number is not the end of the worksheet, _
there must be something already on the worksheet, so build the list.
On Error Resume Next
' Get unique values in range by checking each cell in range.
For Each Cell In Range("D12:D" & endRange)
noDupes.Add Cell.Value, CStr(Cell.Value)
On Error GoTo 0
' Sort the collection alphabetically within the combobox
For i = 1 To noDupes.Count - 1
For j = i + 1 To noDupes.Count
If noDupes(i) > noDupes(j) Then
Swap1 = noDupes(i)
Swap2 = noDupes(j)
noDupes.Add Swap1, before:=j
noDupes.Add Swap2, before:=i
noDupes.Remove i + 1
noDupes.Remove j + 1
' Add items to combobox in their new alphabetical order.
itemIndex = 0
For Each Item In noDupes
UserForm2.ComboBox1.AddItem Item, itemIndex
itemIndex = itemIndex + 1
Else ' otherwise there's no list to populate and the default row is 12
endRange = 12
And just for the record, all object references (cell ranges, comboBox, etc.) are valid and correctly named, so I know it's not there. I also recognize that I have overspecified things by using "UserForm2", but I have done that in the hopes of finding the problem. And the code does execute at the proper times, populating "noDupes" correctly. The problem seems to occur near the end, in the block where noDupes is added to ComboBox1.