User Form: Combo Box - filter out repeats.

[Matthew Balch]

Hi,

My code thus far is:-

Option Explicit
Private Const Sourcename As String = "SubsTargets"
Private Source As Range



Private Sub cmdClose_Click()
Unload Me
End Sub

Private Sub Label2_Click()

End Sub

Private Sub Titles_Click()

End Sub

Private Sub UserForm_Click()

End Sub

Private Sub UserForm_Initialize()
Set Source = Range(Sourcename)
LoadEditors
LoadTitles
End Sub
Private Sub LoadEditors()
Dim markets As New Scripting.Dictionary
Dim index As Long
Dim market As String
For index = 2 To Source.Rows.Count
market = Source.Cells(index, 1)
If Not markets.Exists(market) Then
markets.Add market, market
Editors.AddItem market
End If
Next

End Sub
Private Sub LoadTitles()
Dim markets As New Scripting.Dictionary
Dim index As Long
Dim market As String
For index = 2 To Source.Rows.Count
market = Source.Cells(index, 3)
If Not markets.Exists(market) Then
markets.Add market, market
TitleSelect.AddItem market
End If
Next

End Sub
Private Sub Editors_Change()
LoadTitlesData
End Sub
Private Sub LoadTitlesData()
Dim markets As New Scripting.Dictionary
Dim market As String
Dim index As Long

market = Editors.Value

With TitleSelect
.Clear
For index = 2 To Source.Rows.Count

If Source.Cells(index, 1).Value = market Then
.AddItem Source.Cells(index, 3)

End If
Next
End With

End Sub

Private Sub TitleSelect_Change()
LoadMarketData
End Sub

Private Sub LoadMarketData()

Dim index As Long
Dim titleselection As String


titleselection = TitleSelect.Value
With Titles
.Clear
For index = 2 To Source.Rows.Count
If Source.Cells(index, 3).Value = titleselection Then
.AddItem Source.Cells(index, 4)
.List(.ListCount - 1, 1) = Source.Cells(index, 5)
.List(.ListCount - 1, 2) = Source.Cells(index, 6)
.List(.ListCount - 1, 3) = Source.Cells(index, 7)
.List(.ListCount - 1, 4) = Source.Cells(index, 8)
.List(.ListCount - 1, 5) = Source.Cells(index, 10)
.List(.ListCount - 1, 6) = Source.Cells(index, 11)
.List(.ListCount - 1, 7) = Source.Cells(index, 12)
.List(.ListCount - 1, 8) = Source.Cells(index, 13)

End If
Next
End With
End Sub


What I would like in the LoadTitlesData Sub something that clears and
repeated / duplicate title names. As the LoadTitles Sub works?

Could someone help me please?

TIA
Matthew

 

[Jim Cone]

Hi Matthew,

It is difficult to determine exactly what you are trying to do.
Some suggestions...
Put your question at the top.
(a lot of people who could answer your question are not going to
scroll thru all of the code to find what your problem is)
Explain the results you are getting.
Explain the results you actually want.
Following is some "air" code that might be close to what you want...

Private Sub LoadTitlesData()
Dim dicMarkets As Scripting.Dictionary
Dim Market As String
Dim lngIndex As Long

Market = Editors.Value '???
' Set Source = ???
Set dicMarkets = New Scripting.Dictionary

For lngIndex = 2 To Source.Rows.Count
If dicMarkets.Exists(CStr(Source.Cells(lngIndex, 3).Value)) Then
'it's a duplicate so do something
Else
dicMarkets.Add CStr(Source.Cells(lngIndex, 3)), vbNullString
End If
Next
Set dicMarkets = Nothing
End Sub
'-------------

More...
Index is a word that has a defined meaning in Excel.
You could cause confusion by using it as a variable.
Also, Chip Pearson has some comments on the use of the "New" word...
"Don't Use The New Keyword In A Dim Statement" at...
http://www.cpearson.com/excel/variables.htm

Regards,
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



in message
Hi,
My code thus far is:-
Option Explicit
Private Const Sourcename As String = "SubsTargets"
Private Source As Range

Private Sub cmdClose_Click()
Unload Me
End Sub

Private Sub Label2_Click()

End Sub

Private Sub Titles_Click()

End Sub

Private Sub UserForm_Click()

End Sub

Private Sub UserForm_Initialize()
Set Source = Range(Sourcename)
LoadEditors
LoadTitles
End Sub
Private Sub LoadEditors()
Dim markets As New Scripting.Dictionary
Dim index As Long
Dim market As String
For index = 2 To Source.Rows.Count
market = Source.Cells(index, 1)
If Not markets.Exists(market) Then
markets.Add market, market
Editors.AddItem market
End If
Next

End Sub
Private Sub LoadTitles()
Dim markets As New Scripting.Dictionary
Dim index As Long
Dim market As String
For index = 2 To Source.Rows.Count
market = Source.Cells(index, 3)
If Not markets.Exists(market) Then
markets.Add market, market
TitleSelect.AddItem market
End If
Next

End Sub
Private Sub Editors_Change()
LoadTitlesData
End Sub
Private Sub LoadTitlesData()
Dim markets As New Scripting.Dictionary
Dim market As String
Dim index As Long

market = Editors.Value

With TitleSelect
.Clear
For index = 2 To Source.Rows.Count

If Source.Cells(index, 1).Value = market Then
.AddItem Source.Cells(index, 3)

End If
Next
End With

End Sub

Private Sub TitleSelect_Change()
LoadMarketData
End Sub

Private Sub LoadMarketData()
Dim index As Long
Dim titleselection As String

titleselection = TitleSelect.Value
With Titles
.Clear
For index = 2 To Source.Rows.Count
If Source.Cells(index, 3).Value = titleselection Then
.AddItem Source.Cells(index, 4)
.List(.ListCount - 1, 1) = Source.Cells(index, 5)
.List(.ListCount - 1, 2) = Source.Cells(index, 6)
.List(.ListCount - 1, 3) = Source.Cells(index, 7)
.List(.ListCount - 1, 4) = Source.Cells(index, 8)
.List(.ListCount - 1, 5) = Source.Cells(index, 10)
.List(.ListCount - 1, 6) = Source.Cells(index, 11)
.List(.ListCount - 1, 7) = Source.Cells(index, 12)
.List(.ListCount - 1, 8) = Source.Cells(index, 13)

End If
Next
End With
End Sub


What I would like in the LoadTitlesData Sub something that clears and
repeated / duplicate title names. As the LoadTitles Sub works?
Could someone help me please?
TIA
Matthew