PDA

View Full Version : Ways to remove items from combobox



MountainVogu
05-30-2006, 08:47 PM
G'Day

I question about coding technique.

VBA Excel 2002 SP3

I have a named range on a worksheet that holds a list of unique data items.

The named range is assigned to a variant array and this used to set the list of a combo from the array

The selected option is then used to define the caption of a dynamically (run-time) created label using .Controls.Add "Forms.Textbox.1" "lbl"_labelnumber

eg

selection = orange

lbl_1.caption becomes "orange"

selection apple

lbl_2.caption becomes "apple"


the combobox form (FrmComboSelection) once at least one label exists checks to see if the label caption of each existing label equals one of the combo list values and if so remove it from the list before presenting the user with the list. This is so you can't end up with labels with the same caption.

So my question in essence:

Does anyone know/have a more subtle way of reducing the available list of options in a combobox once an option is selected.



Dim ControlCounter As Long
Dim ListItemcounter As Long

'fill list with data stored in public variant array dimensioned on main form
FrmComboSelection.comboList.List = FrmMain.dataarray


'check each label control with the combos list
For ControlCounter = 1 To FrmMain.Frame1.Controls.Count
With FrmMain.Frame1.Controls(ControlCounter - 1)
If TypeName(FrmMain.Frame1.Controls(ControlCounter - 1)) = "Label" Then
ListItemcounter = 0

'loop through all list items checking for a match with the current labels' caption

Do While ListItemcounter < FrmComboSelection.comboList.ListCount
If .Caption = FrmComboSelection.comboList.List(ListItemcounter) Then
FrmComboSelection.comboList.RemoveItem (ListItemcounter)
'exit do
Else
ListItemcounter = ListItemcounter + 1
End If
Loop
End If
End With
Next




This work perfectly well I was just wondering if there was a better way.

The code above does not exit the do (exit do) if a match is found as my test data contains duplicates.

Cheers

acw
05-30-2006, 11:33 PM
Hi

This could get a bit convoluted, but why don't you remove the item from the combobox at the same time that you set the label.caption? Can be done, but gets messy.


Tony

johnske
05-31-2006, 02:28 AM
Can't have duplicates in a collection, so why not just put it all into a collection?

MountainVogu
05-31-2006, 03:10 AM
Hmm I probably wasn't as clear as I should have been judging by Johnskes' response.

It wasn't an issue of duplicates as the contents of the named range is created from a query to an oracle db using an exists statement to create distinct data. It was simply my test data that had duplicates and this was deliberate to see if once an list item was selected all dups were removed.

My question was, is there a better way to remove items from the combo list once an item is selected rather than my for - next and do loops to compare each entry in the list against each of the already selected items by comparing the .caption parameter of the created controls.

acw
05-31-2006, 06:42 PM
Hi

Do you have the code that allocates the entry to the lable.caption? Include the removal from the combobox in the same process.


Tony

lucas
06-01-2006, 05:45 AM
You don't need code to accomplish this:
Named range:

=OFFSET(Employees!$C$1,0,0,COUNTA(Employees!$C$1:$C$6)-COUNTBLANK(Employees!$C$1:$C$6),1)


I don't remember where I came up with this but it seem to do what you want. See attachment

lucas
06-01-2006, 05:47 AM
Its early for me so I apologize because I didn't realize you were using a form...........

acw
06-01-2006, 03:50 PM
Mountain

Have a look at the attached example. It is not quite the same as yours, in that it keeps changing the caption of the same label. However, each time you make a selection from the combobox, it will update the label caption, then remove that option from those available in the combobox.

HTH

Tony