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
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