Combobox Rowsource

[kirke]

HI all.
I write vba code for combo box.

formCombo.CmbDate.RowSource = "SELECT DISTINCT pp FROM SS ORDER BY
pp;"


but it doesn't work!
In here, pp is the column name and SS is sheet name. Is it wrong? I put
this code in "UserForm_Initialize"
And there's error on "formCombo.Show"
Thank you!

 

[NickHK]

Check out the Help this topic. You will see :
"The RowSource property accepts worksheet ranges from Microsoft Excel."

NickHK

 

[GKeramidas]

i've used something like this:

Dim cbRsource As String
Dim Lastrow As Long
Lastrow = Worksheets("emp").Range("C100").End(xlUp).Row
cbRsource = "'emp'!C2:C" & Lastrow ' which is the sheetname and the range in
column c

With Me.ListBox1
..Visible = True
..RowSource = cbRsource
End With

 

[kirke]

I'm confused with access.
I think i cannot use SQL in excel.
Then does anybody know how to filter the rowsource for combobox?

I mean, if column B have the data : 1 1 2 2 3 3 4 4
Then, If i put rowsource="Sheet1!B:B" then combobox shows 1 1 2 2 3 3 4
4.
I wanna put only 1 2 3 4.
Plz help me.

Thx.

 

[Bob Phillips]

Then you will need to cut some code to create a new list of unique items,
and use that, or load unique items directly, like this

Dim i As Long
Dim cArray As Long
Dim ary

With ActiveSheet
ReDim ary(0 To 0)
cArray = 0
For i = 1 To .Cells(.Rows.Count, "B").End(xlUp).Row
If IsError(Application.Match(Cells(i, "B").Value, ary, 0)) Then
ReDim Preserve ary(0 To cArray)
ary(cArray) = .Cells(i, "B").Value
cArray = cArray + 1
End If
Next i
End With

Me.ComboBox1.List = ary




--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[kirke]

Wow. Thx Bob.
I really appreciate it!!!!!!!!!!

Then you will need to cut some code to create a new list of unique items,
and use that, or load unique items directly, like this

Dim i As Long
Dim cArray As Long
Dim ary

With ActiveSheet
ReDim ary(0 To 0)
cArray = 0
For i = 1 To .Cells(.Rows.Count, "B").End(xlUp).Row
If IsError(Application.Match(Cells(i, "B").Value, ary, 0)) Then
ReDim Preserve ary(0 To cArray)
ary(cArray) = .Cells(i, "B").Value
cArray = cArray + 1
End If
Next i
End With

Me.ComboBox1.List = ary




--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)