Solved Help removing an item from a combobox.

[weilysam]

Hi I have the following code, that puts all names from the name manager into a combo box and removes duplicates and other certain phrases, in this case "CutOffTesting!_FilterD". I want to remove/ignore a second phrase, but can't seem to get it to work.

Private Sub UserForm_Initialize()
' Clear the ComboBox to ensure no duplicate values
ComboBox1.Clear

' Create a collection to store unique base names
Dim BaseNames As Collection
Set BaseNames = New Collection

' Loop through all Name Manager entries and extract base names
Dim nm As Name
For Each nm In ThisWorkbook.Names
' Extract the base name (e.g., "WMP001")
Dim BaseName1 As String
BaseName1 = Left(nm.Name, Len(nm.Name) - 7) ' Remove "Routine" or "Sensory"

' Check if the base name is not already added to the collection
If InStr(1, nm.Name, "CutOffTesting!_FilterD", vbTextCompare) = 0 Then
On Error Resume Next
BaseNames.Add BaseName1, CStr(BaseName1)
On Error GoTo 0
End If
Next nm




' Populate the ComboBox with unique base names
Dim BaseName2 As Variant
For Each BaseName2 In BaseNames
ComboBox1.AddItem BaseName2
Next BaseName2

' Initialize other fields with default values or empty fields
TextBox2.Value = ""
TextBox3.Value = ""
End Sub

 

[moke123]

Use a dictionary with an on error resume next. You can't have duplicates in the key so it errors and resumes, leaving only unique values.

on error resume next
dict.Add  CStr(BaseName1), CStr(BaseName1)

 

[weilysam]

Use a dictionary with an on error resume next. You can't have duplicates in the key so it errors and resumes, leaving only unique values.

on error resume next
dict.Add  CStr(BaseName1), CStr(BaseName1)

I'm not worried about the duplicates, thats already sorted in the current code.
I want to ignore/remove a specific name i.e. this section.

' Check if the base name is not already added to the collection
If InStr(1, nm.Name, "CutOffTesting!_FilterD", vbTextCompare) = 0 Then
On Error Resume Next
BaseNames.Add BaseName1, CStr(BaseName1)
On Error GoTo 0
End If

In this code I am ignoring "CutOffTesting!_FilterD" but i also want to ignore another called "_xlfn." that is appearing in the drop down box.

 

[CJ_London]

if this code in Access or Excel?

If Access, why not just link to the excel file and use a query?

 

[weilysam]

Hi I have the following code, that puts all names from the name manager into a combo box and removes duplicates and other certain phrases, in this case "CutOffTesting!_FilterD". I want to remove/ignore a second phrase, but can't seem to get it to work.

Private Sub UserForm_Initialize()
' Clear the ComboBox to ensure no duplicate values
ComboBox1.Clear

' Create a collection to store unique base names
Dim BaseNames As Collection
Set BaseNames = New Collection

' Loop through all Name Manager entries and extract base names
Dim nm As Name
For Each nm In ThisWorkbook.Names
' Extract the base name (e.g., "WMP001")
Dim BaseName1 As String
BaseName1 = Left(nm.Name, Len(nm.Name) - 7) ' Remove "Routine" or "Sensory"

' Check if the base name is not already added to the collection
If InStr(1, nm.Name, "CutOffTesting!_FilterD", vbTextCompare) = 0 Then
On Error Resume Next
BaseNames.Add BaseName1, CStr(BaseName1)
On Error GoTo 0
End If
Next nm




' Populate the ComboBox with unique base names
Dim BaseName2 As Variant
For Each BaseName2 In BaseNames
ComboBox1.AddItem BaseName2
Next BaseName2

' Initialize other fields with default values or empty fields
TextBox2.Value = ""
TextBox3.Value = ""
End Sub

Have Solved this myself, Code below for anyone wondering,


' Check if the base name is not already added to the collection
If InStr(1, nm.Name, "CutOffTesting!_FilterD", vbTextCompare) = 0 _
And InStr(1, BaseName1, "_xlfn.", vbTextCompare) = 0 Then
On Error Resume Next
BaseNames.Add BaseName1, CStr(BaseName1)
On Error GoTo 0
End If

 

[moke123]

With a dictionary that would be

if dict.exists("CutOffTesting!_FilterD") then dict.remove ("CutOffTesting!_FilterD")
if dict.exists("_xlfn.") then dict.remove ("_xlfn.")

 

[MajP]

With a dictionary that would be

if dict.exists("CutOffTesting!_FilterD") then dict.remove ("CutOffTesting!_FilterD")
if dict.exists("_xlfn.") then dict.remove ("_xlfn.")

The above makes sense but your original statement did not

Use a dictionary with an on error resume next. You can't have duplicates in the key so it errors and resumes, leaving only unique values.

You may want to delete that one. That one defeats the whole point. The whole benefit of doing this in a dictionary as you point out, is so you can check the key without throwing an error. Using error handling to facilitate code execution in vba is IMO not a good idea, and in general frowned upon. That is what you first suggested and the OP settled with. That is why doing this in a dictionary is IMO a better approach. Either that or loop the collection which takes some time if the collection is huge. In other languages like vb.net throwing an error and checking for it is far more acceptable because you have a Try Catch error handling.
Throwing an error in this case works fine and people do it all the time to facilitate code flow, but to me it just seems sloppy.

 

[moke123]

I originally thought OP was trying to filter out duplicates rather than just the 2 strings.

That is what you first suggested and the OP settled with.

OP is using his original instr(), he just added the 2nd string to his original code. That's when I realized what he was doing.
My second posting , not using any error code, just shows how to remove those 2 strings.