Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unique Listbox Scripting Dictionary
I all,
Let me see if i can explain this in a way that will get some help! I have a userform that has a combo box (A) that is filled when the form initializes. Depending on what the user selects it then fills the next combobox (B), my problem arises in that i just want the unique items. No double entries to fill the combo box. A B Grapes Red Flames Peach Yellow Grapes Thompsons Grapes Ruby Reds Peach White The following is some of the code: Private Sub LoadLocation() With lstFieldLocation .Clear Farm = cboFarm.Value For Index = 2 To source.Rows.Count If Farm = source.Cells(Index, 1) Then .AddItem source.Cells(Index, 2) ' Field Location End If Next End With Dim Locations As New Scripting.Dictionary For Index = 2 To source.Rows.Count Loc = source.Cells(Index, "b").Value If Not Locations.Exists(Loc) Then Locations.Add Loc, Loc lstFieldLocation.AddItem Farm End If Next End Sub Please let me know if you need anything else. Thank you so much, Jennifer -- Thank you, Jennifer |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unique Listbox Scripting Dictionary
Private Sub LoadLocation()
Dim Farm, Index As Long, Loc Dim Locations As Object lstFieldLocation.Clear Farm = cboFarm.Value Set Locations = CreateObject("Scripting.Dictionary") With Source For Index = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row If .Cells(Index, "A").Value = Farm Then Loc = .Cells(Index, "B").Value If Not Locations.Exists(Loc) Then Locations.Add Loc, Loc lstFieldLocation.AddItem Loc End If End If Next End With End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jennifer" wrote in message ... I all, Let me see if i can explain this in a way that will get some help! I have a userform that has a combo box (A) that is filled when the form initializes. Depending on what the user selects it then fills the next combobox (B), my problem arises in that i just want the unique items. No double entries to fill the combo box. A B Grapes Red Flames Peach Yellow Grapes Thompsons Grapes Ruby Reds Peach White The following is some of the code: Private Sub LoadLocation() With lstFieldLocation .Clear Farm = cboFarm.Value For Index = 2 To source.Rows.Count If Farm = source.Cells(Index, 1) Then .AddItem source.Cells(Index, 2) ' Field Location End If Next End With Dim Locations As New Scripting.Dictionary For Index = 2 To source.Rows.Count Loc = source.Cells(Index, "b").Value If Not Locations.Exists(Loc) Then Locations.Add Loc, Loc lstFieldLocation.AddItem Farm End If Next End Sub Please let me know if you need anything else. Thank you so much, Jennifer -- Thank you, Jennifer |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unique Listbox Scripting Dictionary
Just some added information.
You asked for the scripting dictionary and Bob gave you that. It can also be done with a built in collection object without the overhead of referencing the scripting runtime: Private Sub LoadLocation() Dim Farm, Index As Long, Loc Dim Locations As Collection lstFieldLocation.Clear Farm = cboFarm.Value Set Locations = New collection With Source For Index = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row If .Cells(Index, "A").Value = Farm Then Loc = .Cells(Index, "B").Value On Error Resume Next locations.Add loc, cstr(loc) if err.Number = 0 then lstFieldLocation.AddItem Loc End If On Error goto 0 End If Next End With End Sub -- Regards, Tom Ogilvy "Jennifer" wrote: I all, Let me see if i can explain this in a way that will get some help! I have a userform that has a combo box (A) that is filled when the form initializes. Depending on what the user selects it then fills the next combobox (B), my problem arises in that i just want the unique items. No double entries to fill the combo box. A B Grapes Red Flames Peach Yellow Grapes Thompsons Grapes Ruby Reds Peach White The following is some of the code: Private Sub LoadLocation() With lstFieldLocation .Clear Farm = cboFarm.Value For Index = 2 To source.Rows.Count If Farm = source.Cells(Index, 1) Then .AddItem source.Cells(Index, 2) ' Field Location End If Next End With Dim Locations As New Scripting.Dictionary For Index = 2 To source.Rows.Count Loc = source.Cells(Index, "b").Value If Not Locations.Exists(Loc) Then Locations.Add Loc, Loc lstFieldLocation.AddItem Farm End If Next End Sub Please let me know if you need anything else. Thank you so much, Jennifer -- Thank you, Jennifer |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Collection VS Scripting.Dictionary | Excel Programming | |||
Scripting.Dictionary question | Excel Programming | |||
Scripting.Dictionary | Excel Programming | |||
VBA - running scripting dictionary (error!) | Excel Programming | |||
VBA question - Set reference to run Scripting.Dictionary | Excel Programming |