sort alphabetically a combo box list

[Valeria]

Dear experts,
I am using a combobox to make selections that points to a database where the
data is not sorted out alphabetically and need to stay this way.
Is there a way I can still show the entries sorted alphabetically in the
scroll down of the combobox only? I am suing Excel 2003.

Many thanks for your help,
Best regards,

 

[ryguy7272]

Pretty generic question... For things that are googleable (is that a word),
it is probably better just to search for what you want. That's what I did to
find this link:
http://www.ozgrid.com/forum/showthread.php?t=24802

#1) View the code
#2) Download the sample file...named '24802.xls'

I have another version of this, which I use from time to time, but it is a
little difficult for me to explain how to set everything up, including the
UserForm. If you can download a sample file, like the one in the link that I
posted, that would be the obvious choice.


Regards,
Ryan--

 

[KUMPFfrog]

Steve (or anyone reading), can you please help.
I don't know much about VB. Could you tell me how this code can be modified
for what i already have? - following was given to me by another user.
Private Sub UserForm_Initialize()
Dim lr As Long, i As Long
lr = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row
With ActiveSheet
For i = 8 To lr
If .Cells(i, 3) <> "" And IsNumeric(.Cells(i, 3)) Then
Me.JobsList.AddItem .Cells(i, 3)
End If
Next
End With
End Sub

this code puts exactly the data i want in the combobox - i am just trying to
sort it now.
Thank for any help you can provide.

 

[KUMPFfrog]

Thank You, Thank You, Thank You!!!!

works perfect!!!! U Rock

 

[KUMPFfrog]

need so more assistance if you don't mind.
the code you gave me worked perfect, but i am now trying to use it for
another combobox that is set up as cascading. the second combobox (cmbInv)
is populated after a selection is made in the original combobox (cmbJobs). I
thought i could just change a few things in your code to make it work, but
it's hanging up on line 25.

basically "iList" needs to show values from column "n" if column "s" matches
the selected value in "cmbJobs".

01 Private Sub cmbJobs_Change()
02 cmbInv.Enabled = True
03 cmbInv.BackColor = &H80000005
04 Const adVarChar = 200
05 Const MaxCharacters = 255
06
07 Set InvList = CreateObject("ADOR.Recordset")
08 InvList.Fields.Append "iList", adVarChar, MaxCharacters
09 InvList.Open
10
11 Dim lr As Long, i As Long
12 lr = ActiveSheet.Cells(Rows.Count, "n").End(xlUp).Row
13 With ActiveSheet
14 For i = 8 To lr
15 If .Cells(i, "s") = cmbJobs.Value Then
16 InvList.AddNew
17 InvList("iList") = Cells(i, "n").Value
18 InvList.Update
19 End If
20 Next
21 End With
22
23 InvList.Sort = "iList"
24
25 InvList.MoveFirst
26 Do Until DataList.EOF
27 Me.cmbInv.AddItem DataList.Fields.Item("iList")
28 InvList.MoveNext
29 Loop
30
31 Set InvList = Nothing
32 End Sub

what am i doing wrong here?????

 

[KUMPFfrog]

no difference - same error.
i guess for "cmbinv" - i don't really need to apply a sort to it, i just
need to populate the list based off the "cmbJobs" value. that would simplify
things, but I still can't figured oput how to do that even.