i want to retrieve items from a column of data from a table and add the items to a combo box.?

Question

the problem is that if items are repeated in the column in the table, they get added multiple times to the combo box. Please help me with the problem...

Answer 1

Here is one method, albeit less than elegant, that will always create a new, sorted, combobox list without duplicates.



This example uses column A as the column containing the raw data. If your column is not 'A', change the two "A" references to your column letter, i.e. "C", "M", etc. Also, if your combobox is not 'ComboBox1', change that reference in line 20 to your actual combobox name.



Then, copy the event handler to the userform's code module



Public curCell

Private Sub UserForm_Activate()

Dim i, j, LastRow

LastRow = Range("A" & Rows.Count).End(xlUp).Row

Application.ScreenUpdating = False

Range("IV:IV").EntireColumn.Hidden = True

Range("IV:IV").ClearContents

curCell = ActiveCell.Address(0, 0)

For i = 1 To LastRow

If Application.CountIf(Range("IV:IV"), Cells(i, "A").Value) = 0 Then

Range("IV" & Rows.Count).End(xlUp). Offset(1).Value = Cells(i, "A").Value

End If

Next

Columns("IV:IV").Select

Selection.Sort Key1:=Range("IV1"), Order1:=xlAscending, Header:=xlGuess, _

OrderCustom:=1, dataOption1:=xlSortNormal

Range(curCell).Select

LastRowIV = Range("IV" & Rows.Count).End(xlUp).Row

For i = 1 To LastRowIV

Me.ComboBox1.AddItem Cells(i, "IV").Value

Next

End Sub



Alternatively, you can use this method. However, the list will be unsorted. It would be possible to dump the combobox list into an array, sort the array, and repopulate the list. Unfortunately, I am late for an appointment and have to end it here.



Private Sub UserForm_Activate()

Dim i, LastRow

LastRow = Range("A" & Rows.Count).End(xlUp).Row

Application.ScreenUpdating = False

For i = 1 To LastRow

Me.ComboBox1.Text = Sheets("Sheet1").Cells(i, 1)

If Not Me.ComboBox1.MatchFound And Not Me.ComboBox1.Text = "" _

Then Me.ComboBox1.AddItem Me.ComboBox1.Text

Next

End Sub

Answer 2

There are different ways to create the list of items displayed by a combo box or a list box on a VBA UserForm. One way is to "hard code" the list into the UserForm's Initialize event procedure using the .AddItem method. This is fine if you know what the contents of the list should be, and if is not going to change regularly. For multicolumn list the following program will prove useful.

Dim i As Integer

rst.Open "SELECT [Code], [Country] FROM tblISOCOuntryCodes ORDER BY [Country];", _

cnn, adOpenStatic

rst.MoveFirst

i = 0

With Me.ListBox1

.Clear

Do

.AddItem

.List(i, 0) = rst![Code]

.List(i, 1) = rst![Country]

i = i + 1

rst.MoveNext

Loop Until rst.EOF

End With



For further information visit the link

http://www.fontstuff.com/vba/vbatut10.htm

Answer 3

which may be the clean thanks to do i think, close the document then reopen to append, Incase append is what's complicated you it skill to open the document and some thing your going to be outputting to that document will be written on the end of the document. it really is been awhile considering the fact that i have performed any VBing so i am going to't truly bust out some code for you, i wager i'm no longer precisely know-how what your major difficulty is. Is it the concept of what's happening? Or is it the source code?

Answer 4

Use this:



SELECT DISTINCT FROM

ⓘ

This content was originally posted on Y! Answers, a Q&A website that shut down in 2021.

about - legalese

Loading...