-
Jun 28th, 2007, 03:41 AM
#1
Thread Starter
Frenzied Member
Checking if duplicate element in combobox
Hi There,
I have been populating my combobox using this method, is there a faster way of asking the combobox if an element is duplicate? and if it is not to add it in. I know in VB.Net there is a lstbox.items.contains("BAH") as boolean, but is there a similar method in VBA?
Cheers
Ken
Code:
Private Sub UserForm_Initialize()
'We need to load up the combo box with the class of chemical
Dim i As Integer
Dim j As Integer
Dim str As String
Dim Unique As Boolean
Unique = True
Me.cboChemical.Clear
'We need to add the option of all items
Me.cboChemical.AddItem ("List All")
For i = 9 To 88
str = Sheets("Solvent").Cells(i, 1)
If str = "" Then
'Do nothing
Else
'Probably a more elegant way of doing this
Unique = True
For j = 0 To Me.cboChemical.ListCount - 1
If str = Me.cboChemical.List(j) Then
'it already exists on the list, exit
Unique = False
End If
Next
If Unique = True Then
Me.cboChemical.AddItem (str)
End If
End If
Next
End Sub
If you find my thread helpful, please remember to rate me
-
Jun 28th, 2007, 04:36 AM
#2
Re: Checking if duplicate element in combobox
I don't know about simpler, but assigning it to an array and then loading the combobox with the list property will definitely be a lot faster.
-
Jun 28th, 2007, 05:17 AM
#3
Thread Starter
Frenzied Member
Re: Checking if duplicate element in combobox
how do you assign it to an array?
If you find my thread helpful, please remember to rate me
-
Jun 28th, 2007, 06:59 AM
#4
Re: Checking if duplicate element in combobox
in vb you can use the sendmessage API with cbofindexactstring to check for an item in a combobox, but i don't know if it will work with forms 2.0 control combobox used in excel
do a search in the classic vb forum then see if it will work
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Jun 28th, 2007, 08:46 AM
#5
Thread Starter
Frenzied Member
Re: Checking if duplicate element in combobox
thanks for your reply
hmm. Is it possible to feed a control into a subroutine and get it to return a value? If it so, how do i do that?
If you find my thread helpful, please remember to rate me
-
Jun 28th, 2007, 11:17 AM
#6
Re: Checking if duplicate element in combobox
to set a combobox from an array, assign the list or column property to the array name. You load the array to start with exactly like the code you used there, just replace your combo reference with the array.
-
Jun 28th, 2007, 07:50 PM
#7
Thread Starter
Frenzied Member
Re: Checking if duplicate element in combobox
and how do i do that? sorry, i am abit rusty
If you find my thread helpful, please remember to rate me
-
Jun 29th, 2007, 08:53 AM
#8
Re: Checking if duplicate element in combobox
which part? combo1.list = myarray()
or the filling in the array part?
-
Jun 30th, 2007, 08:28 AM
#9
Re: Checking if duplicate element in combobox
you can use the matchfound property of the combobox to know if the item already exists
try like this
vb Code:
For i = 9 To 88
Me.cboChemical.Text = Sheets("Solvent").Cells(i, 1)
If Not Me.cboChemical.MatchFound And Not Me.cboChemical.Text Then Me.cboChemical.AddItem Me.cboChemical.Text
End If
Next
Last edited by westconn1; Jun 30th, 2007 at 09:03 AM.
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Jun 30th, 2007, 10:18 AM
#10
Re: Checking if duplicate element in combobox
If your combobox is on the worksheet itself, populate it using the ListFillRange property, which is just a string corresponding to a cell range.
If your combobox is on a userform, use the RowSource property, which does the same thing.
Then you can check your Excel range for duplicates and get rid of them, which will be much easier. One way, if you don't mind sorting, is to sort your list and then you only have to check each item against the next one.
Or you could use Find.
Or, you could use the following:
Code:
Range("A1:A10").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Obviously you need to set the range, and you may not want to FilterInPlace. Also, you need to have a header on the column. It's the same as using the Filter option from the Data menu.
Personally, I would do this latter option, then set your combobox to populate from whatever range is left - you could just do a Find to get the first blank cell, for example.
A few ideas, anyway.
zaza
Last edited by zaza; Jun 30th, 2007 at 10:36 AM.
-
Jul 2nd, 2007, 03:54 AM
#11
Thread Starter
Frenzied Member
Re: Checking if duplicate element in combobox
Originally Posted by westconn1
you can use the matchfound property of the combobox to know if the item already exists
try like this
vb Code:
For i = 9 To 88
Me.cboChemical.Text = Sheets("Solvent").Cells(i, 1)
If Not Me.cboChemical.MatchFound And Not Me.cboChemical.Text Then Me.cboChemical.AddItem Me.cboChemical.Text
End If
Next
Hiya,
the code does not work
If you find my thread helpful, please remember to rate me
-
Jul 2nd, 2007, 04:44 AM
#12
Re: Checking if duplicate element in combobox
i did test it (with some alterations) before posting, what part does not work
edit: a couple of copy errors
vb Code:
For i = 9 To 88
Me.cboChemical.Text = Sheets("Solvent").Cells(i, 1)
If Not Me.cboChemical.MatchFound And Not Me.cboChemical.Text = "" Then Me.cboChemical.AddItem Me.cboChemical.Text
Next
Last edited by westconn1; Jul 2nd, 2007 at 04:53 AM.
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|