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,
--
Valeria

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--

--
RyGuy


"Valeria" wrote:

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,
--
Valeria

One option would be to create a disconnected recordset to sort the data and
then populate your combobox. I'm not sure how you plan to retrieve your
data from the database. For the example below, I just have a list of names
that are not sorted in rows 1 to 10 in Column A on the active sheet. I
created a UserForm1 with a ComboBox1 and use the sub below to populate
ComboBox1 with my list in alphabetical order when the user form is
activated.

'---------------------------------------------------------------------------

Private Sub UserForm_Activate()

Const adVarChar = 200
Const MaxCharacters = 255

Dim R As Integer

Set DataList = CreateObject("ADOR.Recordset")
DataList.Fields.Append "MyList", adVarChar, MaxCharacters
DataList.Open

For R = 1 To 10
DataList.AddNew
DataList("MyList") = Cells(R, 1).Value
DataList.Update
Next R

DataList.Sort = "MyList"

DataList.MoveFirst
Do Until DataList.EOF
ComboBox1.AddItem DataList.Fields.Item("MyList")
DataList.MoveNext
Loop

Set DataList = Nothing

End Sub

'---------------------------------------------------------------------------


Steve Yandl



"Valeria" wrote in message
...
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,
--
Valeria

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.

"Steve Yandl" wrote:

One option would be to create a disconnected recordset to sort the data and
then populate your combobox. I'm not sure how you plan to retrieve your
data from the database. For the example below, I just have a list of names
that are not sorted in rows 1 to 10 in Column A on the active sheet. I
created a UserForm1 with a ComboBox1 and use the sub below to populate
ComboBox1 with my list in alphabetical order when the user form is
activated.

'---------------------------------------------------------------------------

Private Sub UserForm_Activate()

Const adVarChar = 200
Const MaxCharacters = 255

Dim R As Integer

Set DataList = CreateObject("ADOR.Recordset")
DataList.Fields.Append "MyList", adVarChar, MaxCharacters
DataList.Open

For R = 1 To 10
DataList.AddNew
DataList("MyList") = Cells(R, 1).Value
DataList.Update
Next R

DataList.Sort = "MyList"

DataList.MoveFirst
Do Until DataList.EOF
ComboBox1.AddItem DataList.Fields.Item("MyList")
DataList.MoveNext
Loop

Set DataList = Nothing

End Sub

'---------------------------------------------------------------------------


Steve Yandl



"Valeria" wrote in message
...
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,
--
Valeria

Try this.

'-----------------------------------------

Private Sub UserForm_Initialize()

Const adVarChar = 200
Const MaxCharacters = 255

Set DataList = CreateObject("ADOR.Recordset")
DataList.Fields.Append "MyList", adVarChar, MaxCharacters
DataList.Open

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
DataList.AddNew
DataList("MyList") = Cells(i, 3).Value
DataList.Update
End If
Next
End With

DataList.Sort = "MyList"

DataList.MoveFirst
Do Until DataList.EOF
Me.JobsList.AddItem DataList.Fields.Item("MyList")
DataList.MoveNext
Loop

Set DataList = Nothing
End Sub

'-------------------------------------------

Steve Yandl



"KUMPFfrog" wrote in message
...
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.

"Steve Yandl" wrote:

One option would be to create a disconnected recordset to sort the data
and
then populate your combobox. I'm not sure how you plan to retrieve your
data from the database. For the example below, I just have a list of
names
that are not sorted in rows 1 to 10 in Column A on the active sheet. I
created a UserForm1 with a ComboBox1 and use the sub below to populate
ComboBox1 with my list in alphabetical order when the user form is
activated.

'---------------------------------------------------------------------------

Private Sub UserForm_Activate()

Const adVarChar = 200
Const MaxCharacters = 255

Dim R As Integer

Set DataList = CreateObject("ADOR.Recordset")
DataList.Fields.Append "MyList", adVarChar, MaxCharacters
DataList.Open

For R = 1 To 10
DataList.AddNew
DataList("MyList") = Cells(R, 1).Value
DataList.Update
Next R

DataList.Sort = "MyList"

DataList.MoveFirst
Do Until DataList.EOF
ComboBox1.AddItem DataList.Fields.Item("MyList")
DataList.MoveNext
Loop

Set DataList = Nothing

End Sub

'---------------------------------------------------------------------------


Steve Yandl



"Valeria" wrote in message
...
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,
--
Valeria

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

works perfect!!!! U Rock

"Steve Yandl" wrote:

Try this.

'-----------------------------------------

Private Sub UserForm_Initialize()

Const adVarChar = 200
Const MaxCharacters = 255

Set DataList = CreateObject("ADOR.Recordset")
DataList.Fields.Append "MyList", adVarChar, MaxCharacters
DataList.Open

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
DataList.AddNew
DataList("MyList") = Cells(i, 3).Value
DataList.Update
End If
Next
End With

DataList.Sort = "MyList"

DataList.MoveFirst
Do Until DataList.EOF
Me.JobsList.AddItem DataList.Fields.Item("MyList")
DataList.MoveNext
Loop

Set DataList = Nothing
End Sub

'-------------------------------------------

Steve Yandl



"KUMPFfrog" wrote in message
...
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.

"Steve Yandl" wrote:

One option would be to create a disconnected recordset to sort the data
and
then populate your combobox. I'm not sure how you plan to retrieve your
data from the database. For the example below, I just have a list of
names
that are not sorted in rows 1 to 10 in Column A on the active sheet. I
created a UserForm1 with a ComboBox1 and use the sub below to populate
ComboBox1 with my list in alphabetical order when the user form is
activated.

'---------------------------------------------------------------------------

Private Sub UserForm_Activate()

Const adVarChar = 200
Const MaxCharacters = 255

Dim R As Integer

Set DataList = CreateObject("ADOR.Recordset")
DataList.Fields.Append "MyList", adVarChar, MaxCharacters
DataList.Open

For R = 1 To 10
DataList.AddNew
DataList("MyList") = Cells(R, 1).Value
DataList.Update
Next R

DataList.Sort = "MyList"

DataList.MoveFirst
Do Until DataList.EOF
ComboBox1.AddItem DataList.Fields.Item("MyList")
DataList.MoveNext
Loop

Set DataList = Nothing

End Sub

'---------------------------------------------------------------------------


Steve Yandl



"Valeria" wrote in message
...
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,
--
Valeria

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?????

"Steve Yandl" wrote:

Try this.

'-----------------------------------------

Private Sub UserForm_Initialize()

Const adVarChar = 200
Const MaxCharacters = 255

Set DataList = CreateObject("ADOR.Recordset")
DataList.Fields.Append "MyList", adVarChar, MaxCharacters
DataList.Open

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
DataList.AddNew
DataList("MyList") = Cells(i, 3).Value
DataList.Update
End If
Next
End With

DataList.Sort = "MyList"

DataList.MoveFirst
Do Until DataList.EOF
Me.JobsList.AddItem DataList.Fields.Item("MyList")
DataList.MoveNext
Loop

Set DataList = Nothing
End Sub

'-------------------------------------------

Steve Yandl



"KUMPFfrog" wrote in message
...
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.

"Steve Yandl" wrote:

One option would be to create a disconnected recordset to sort the data
and
then populate your combobox. I'm not sure how you plan to retrieve your
data from the database. For the example below, I just have a list of
names
that are not sorted in rows 1 to 10 in Column A on the active sheet. I
created a UserForm1 with a ComboBox1 and use the sub below to populate
ComboBox1 with my list in alphabetical order when the user form is
activated.

'---------------------------------------------------------------------------

Private Sub UserForm_Activate()

Const adVarChar = 200
Const MaxCharacters = 255

Dim R As Integer

Set DataList = CreateObject("ADOR.Recordset")
DataList.Fields.Append "MyList", adVarChar, MaxCharacters
DataList.Open

For R = 1 To 10
DataList.AddNew
DataList("MyList") = Cells(R, 1).Value
DataList.Update
Next R

DataList.Sort = "MyList"

DataList.MoveFirst
Do Until DataList.EOF
ComboBox1.AddItem DataList.Fields.Item("MyList")
DataList.MoveNext
Loop

Set DataList = Nothing

End Sub

'---------------------------------------------------------------------------


Steve Yandl



"Valeria" wrote in message
...
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,
--
Valeria

I only had time to glance at this but spotted something to change.

In lines 12 and 17, change "n" to 14 for the cell reference.

Likewise, in line 15, change "s" to 19.


Hopefully, that was the only issue and it will run.


Steve Yandl



"KUMPFfrog" wrote in message
...
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?????

"Steve Yandl" wrote:

Try this.

'-----------------------------------------

Private Sub UserForm_Initialize()

Const adVarChar = 200
Const MaxCharacters = 255

Set DataList = CreateObject("ADOR.Recordset")
DataList.Fields.Append "MyList", adVarChar, MaxCharacters
DataList.Open

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
DataList.AddNew
DataList("MyList") = Cells(i, 3).Value
DataList.Update
End If
Next
End With

DataList.Sort = "MyList"

DataList.MoveFirst
Do Until DataList.EOF
Me.JobsList.AddItem DataList.Fields.Item("MyList")
DataList.MoveNext
Loop

Set DataList = Nothing
End Sub

'-------------------------------------------

Steve Yandl



"KUMPFfrog" wrote in message
...
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.

"Steve Yandl" wrote:

One option would be to create a disconnected recordset to sort the
data
and
then populate your combobox. I'm not sure how you plan to retrieve
your
data from the database. For the example below, I just have a list of
names
that are not sorted in rows 1 to 10 in Column A on the active sheet.
I
created a UserForm1 with a ComboBox1 and use the sub below to populate
ComboBox1 with my list in alphabetical order when the user form is
activated.

'---------------------------------------------------------------------------

Private Sub UserForm_Activate()

Const adVarChar = 200
Const MaxCharacters = 255

Dim R As Integer

Set DataList = CreateObject("ADOR.Recordset")
DataList.Fields.Append "MyList", adVarChar, MaxCharacters
DataList.Open

For R = 1 To 10
DataList.AddNew
DataList("MyList") = Cells(R, 1).Value
DataList.Update
Next R

DataList.Sort = "MyList"

DataList.MoveFirst
Do Until DataList.EOF
ComboBox1.AddItem DataList.Fields.Item("MyList")
DataList.MoveNext
Loop

Set DataList = Nothing

End Sub

'---------------------------------------------------------------------------


Steve Yandl



"Valeria" wrote in message
...
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,
--
Valeria

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.

"Steve Yandl" wrote:

I only had time to glance at this but spotted something to change.

In lines 12 and 17, change "n" to 14 for the cell reference.

Likewise, in line 15, change "s" to 19.


Hopefully, that was the only issue and it will run.


Steve Yandl



"KUMPFfrog" wrote in message
...
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?????

"Steve Yandl" wrote:

Try this.

'-----------------------------------------

Private Sub UserForm_Initialize()

Const adVarChar = 200
Const MaxCharacters = 255

Set DataList = CreateObject("ADOR.Recordset")
DataList.Fields.Append "MyList", adVarChar, MaxCharacters
DataList.Open

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
DataList.AddNew
DataList("MyList") = Cells(i, 3).Value
DataList.Update
End If
Next
End With

DataList.Sort = "MyList"

DataList.MoveFirst
Do Until DataList.EOF
Me.JobsList.AddItem DataList.Fields.Item("MyList")
DataList.MoveNext
Loop

Set DataList = Nothing
End Sub

'-------------------------------------------

Steve Yandl



"KUMPFfrog" wrote in message
...
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.

"Steve Yandl" wrote:

One option would be to create a disconnected recordset to sort the
data
and
then populate your combobox. I'm not sure how you plan to retrieve
your
data from the database. For the example below, I just have a list of
names
that are not sorted in rows 1 to 10 in Column A on the active sheet.
I
created a UserForm1 with a ComboBox1 and use the sub below to populate
ComboBox1 with my list in alphabetical order when the user form is
activated.

'---------------------------------------------------------------------------

Private Sub UserForm_Activate()

Const adVarChar = 200
Const MaxCharacters = 255

Dim R As Integer

Set DataList = CreateObject("ADOR.Recordset")
DataList.Fields.Append "MyList", adVarChar, MaxCharacters
DataList.Open

For R = 1 To 10
DataList.AddNew
DataList("MyList") = Cells(R, 1).Value
DataList.Update
Next R

DataList.Sort = "MyList"

DataList.MoveFirst
Do Until DataList.EOF
ComboBox1.AddItem DataList.Fields.Item("MyList")
DataList.MoveNext
Loop

Set DataList = Nothing

End Sub

'---------------------------------------------------------------------------


Steve Yandl



"Valeria" wrote in message
...
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,
--
Valeria

I may have more time tomorrow or Friday to take a better look at this. One
thing to try in the interim is to change the sub to the 'AfterUpdate' event
of your first combo box rather than the 'Change' event.

Steve Yandl


"KUMPFfrog" wrote in message
...
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.

"Steve Yandl" wrote:

I only had time to glance at this but spotted something to change.

In lines 12 and 17, change "n" to 14 for the cell reference.

Likewise, in line 15, change "s" to 19.


Hopefully, that was the only issue and it will run.


Steve Yandl



"KUMPFfrog" wrote in message
...
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?????

"Steve Yandl" wrote:

Try this.

'-----------------------------------------

Private Sub UserForm_Initialize()

Const adVarChar = 200
Const MaxCharacters = 255

Set DataList = CreateObject("ADOR.Recordset")
DataList.Fields.Append "MyList", adVarChar, MaxCharacters
DataList.Open

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
DataList.AddNew
DataList("MyList") = Cells(i, 3).Value
DataList.Update
End If
Next
End With

DataList.Sort = "MyList"

DataList.MoveFirst
Do Until DataList.EOF
Me.JobsList.AddItem DataList.Fields.Item("MyList")
DataList.MoveNext
Loop

Set DataList = Nothing
End Sub

'-------------------------------------------

Steve Yandl



"KUMPFfrog" wrote in message
...
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.

"Steve Yandl" wrote:

One option would be to create a disconnected recordset to sort the
data
and
then populate your combobox. I'm not sure how you plan to retrieve
your
data from the database. For the example below, I just have a list
of
names
that are not sorted in rows 1 to 10 in Column A on the active
sheet.
I
created a UserForm1 with a ComboBox1 and use the sub below to
populate
ComboBox1 with my list in alphabetical order when the user form is
activated.

'---------------------------------------------------------------------------

Private Sub UserForm_Activate()

Const adVarChar = 200
Const MaxCharacters = 255

Dim R As Integer

Set DataList = CreateObject("ADOR.Recordset")
DataList.Fields.Append "MyList", adVarChar, MaxCharacters
DataList.Open

For R = 1 To 10
DataList.AddNew
DataList("MyList") = Cells(R, 1).Value
DataList.Update
Next R

DataList.Sort = "MyList"

DataList.MoveFirst
Do Until DataList.EOF
ComboBox1.AddItem DataList.Fields.Item("MyList")
DataList.MoveNext
Loop

Set DataList = Nothing

End Sub

'---------------------------------------------------------------------------


Steve Yandl



"Valeria" wrote in message
...
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,
--
Valeria