VBA USERFORM : Search and Find a String and Remove from the Combobox List

[sachinns]

Hi Friends,


I have a combobox in User form where the data is taking from a sheet called "Inconsistency Order" and the combobox name is cmbSelChartAttr . I have so many values in the droplist. The values which ever is having a string "- check" in the end , It should not be added to the combobox List.


Example : We have values like : ABC , DEF , GHI , ABC - check , DEF , GHI - check .


So In our combobox droplist , The droplist will only show ABC , DEF , GHI .


Someone please help to add code for this in my existing Code.

Function formInitialize(ws As Worksheet)
Set ws = ThisWorkbook.Worksheets("Inconsistency Order")
If ws.Visible = True Then
ws.Activate
Else
ws.Visible = xlSheetVisible
ws.Activate
End If
Dim i, lc
Dim j
Dim cell As Range
Dim tecAttrVal As String
lc = ws.Cells(1, Columns.Count).End(xlToLeft).Column
i = 0


Dim c As Collection
Set c = New Collection


On Error Resume Next




For i = i + 1 To lc
tecAttrVal = Cells(1, i).Value
c.Add tecAttrVal, tecAttrVal
If Err.Number = 0 Then
cmbSelChartAttr.AddItem tecAttrVal
Else
Err.Clear
End If


Next i
Me.Label3.Visible = False
Me.lsbOrderSet4SelAttr.Visible = False


End Function

Thanks in Advance

 

[sykes]

This might do the trick:

Function formInitialize(ws As Worksheet)
Set ws = ThisWorkbook.Worksheets("Inconsistency Order")
If ws.Visible = True Then
ws.Activate
Else
ws.Visible = xlSheetVisible
ws.Activate
End If
Dim i, lc
Dim j
Dim cell As Range
Dim tecAttrVal As String
lc = ws.Cells(1, Columns.Count).End(xlToLeft).Column
i = 0


Dim c As Collection
Set c = New Collection


On Error Resume Next




For i = i + 1 To lc
tecAttrVal = Cells(1, i).Value
c.Add tecAttrVal, tecAttrVal
If Err.Number = 0 And Right(tecAttrVal, 5) <> "check" Then
cmbSelChartAttr.AddItem tecAttrVal
Else
Err.Clear
End If


Next i
Me.Label3.Visible = False
Me.lsbOrderSet4SelAttr.Visible = False


End Function

...not tested, though...

 

[Norie]

Where are the values you want in the dropdown located on the sheet 'Inconsistency Order'?

Is it always that sheet you want the values from?

What if there is unique value with ' check' after it?

Would you want to list that in the dropdown?

 

[sachinns]

@sykes

Thanks for the reply. It is working but some issues are there.

Example : My values in the sheets are ABC , DEF , GHI , ABC Mandatory Check , DEF , GHI - Check.

The Values i wanted in my Dropdown is : ABC , DEF , GHI , ABC Mandatory Check .

So as per your code the value "ABC Mandatory Check" is not coming in the drop list.

I hope you understand i point.

 

[sachinns]

@Norie

Where are the values you want in the dropdown located on the sheet 'Inconsistency Order'?
Example : ABC , DEF , GHI , ABC Mandatory Check , DEF , GHI - Check.

Is it always that sheet you want the values from?
Yes

What if there is unique value with ' check' after it?
I dont want any values which has "- Check" .

Would you want to list that in the dropdown?
I dont want to link any values which consists of "- Check"

 

[sachinns]

Thank You . It worked ! I modified little. But perfect.

 

[sykes]

If I understand correctly, you wish to have any values - except those ending in "- check" ...so the hyphen character is important to omit, too? That's the only difference i can see in your explaation.:

Function formInitialize(ws As Worksheet)
Set ws = ThisWorkbook.Worksheets("Inconsistency Order")
If ws.Visible = True Then
ws.Activate
Else
ws.Visible = xlSheetVisible
ws.Activate
End If
Dim i, lc
Dim j
Dim cell As Range
Dim tecAttrVal As String
lc = ws.Cells(1, Columns.Count).End(xlToLeft).Column
i = 0


Dim c As Collection
Set c = New Collection


On Error Resume Next




For i = i + 1 To lc
tecAttrVal = Cells(1, i).Value
c.Add tecAttrVal, tecAttrVal
If Err.Number = 0 And Right(tecAttrVal, 7) <> "- check" Then
cmbSelChartAttr.AddItem tecAttrVal
Else
Err.Clear
End If


Next i
Me.Label3.Visible = False
Me.lsbOrderSet4SelAttr.Visible = False


End Function

 

[sachinns]

Thanks. Working Perfectly.

 

[sykes]

Pleasure. Thanks for the feedback.