G'day there One & All,

Here I am back again, with another problem. This time while trying
to sort with a custom list.

Here's the code I'm trying to use:

Private Sub oBtnRank_Click()
If oBtnRank Then gRng.Sort _
key1:=Range("Rank"), _
order1:=srtDir, _
header:=xlYes, _
ordercustom:=cstmSrtRngCnt
gStr = "Rank"
End Sub

This is the code from one of the OptionButtons on my userform.
Except for the "ordercustom" part, the code is exactly the same as for 3
other optionbuttons, but they sort my range alphabetically (which is
what they're supposed to do).

This particular option is supposed to sort via my custom list, but
for some reason it insists on sorting alphabetically and ignoring my
custom list.

To explain a little; gRng has been "set" to a named dynamic range.
Range("Rank") is the heading for the third column in this range.
"srtDir" is a string which has been set to Ascending or Descending.
"cstmSrtRngCnt" is a numeric variable containing the count of custom
lists - it's determined by

If Not dSht.Range("wrkRanks").Cells.Count = 0 Then
Application.AddCustomList (dSht.Range("wrkRanks"))
End If
cstmSrtRngCnt = Application.CustomListCount

this is from the "Auto_Open" procedure.

My best friend Google has provided me with one or two examples, but not
a lot that seems applicable. There are heaps of descriptions of how to
use custom lists directly from the sheet, but I've not found a lot that
demonstrates how to do it in VB.

If anyone can shed some light on this for me I'd be very grateful.

Thanks very much for listening,
--
Ken McLennan
Qld, Australia

Hi Ken,

See MSKB article # 134913:

XL: GetCustomListNum Returns Unexpected List Number

http://support.microsoft.com/default.aspx?kbid=134913


---
Regards,
Norman



"Ken McLennan" wrote in message
.. .
G'day there One & All,

Here I am back again, with another problem. This time while trying
to sort with a custom list.

Here's the code I'm trying to use:

Private Sub oBtnRank_Click()
If oBtnRank Then gRng.Sort _
key1:=Range("Rank"), _
order1:=srtDir, _
header:=xlYes, _
ordercustom:=cstmSrtRngCnt
gStr = "Rank"
End Sub

This is the code from one of the OptionButtons on my userform.
Except for the "ordercustom" part, the code is exactly the same as for 3
other optionbuttons, but they sort my range alphabetically (which is
what they're supposed to do).

This particular option is supposed to sort via my custom list, but
for some reason it insists on sorting alphabetically and ignoring my
custom list.

To explain a little; gRng has been "set" to a named dynamic range.
Range("Rank") is the heading for the third column in this range.
"srtDir" is a string which has been set to Ascending or Descending.
"cstmSrtRngCnt" is a numeric variable containing the count of custom
lists - it's determined by

If Not dSht.Range("wrkRanks").Cells.Count = 0 Then
Application.AddCustomList (dSht.Range("wrkRanks"))
End If
cstmSrtRngCnt = Application.CustomListCount

this is from the "Auto_Open" procedure.

My best friend Google has provided me with one or two examples, but not
a lot that seems applicable. There are heaps of descriptions of how to
use custom lists directly from the sheet, but I've not found a lot that
demonstrates how to do it in VB.

If anyone can shed some light on this for me I'd be very grateful.

Thanks very much for listening,
--
Ken McLennan
Qld, Australia

G'day there Norman,

See MSKB article # 134913:

XL: GetCustomListNum Returns Unexpected List Number

http://support.microsoft.com/default.aspx?kbid=134913

Thanks very much for your advice. I went to the site and checked
what it had to say. As it turned out, altering the index number was
something I'd already tried but had no success. I rechecked everything
I was doing and tried for about an hour and a half to apply the
information that M'soft stated.

After many, many swear words and multiple rechecking of the code,
it suddenly struck me that the method may not have been the problem, but
the data. I don't know what made me think of it, but on checking I found
that my custom sort had turned to crap because the data I was using
hadn't been validated. It was just a bunch of entries I'd typed in to
give myself some sample data.

Hence I was trying to sort data that wasn't recognised by my
custom list!! DOH!!!

Now that I've embarassed myself, and wasted hours & hours over
several days, I'll move on =).

Thanks once again for your assistance. Your suggestion was spot
on, as that WAS one of the issues. I needed to get that correct before I
was prompted to move on to check my data.

See ya
Ken McLennan
Qld, Australia