Custom list to sort worksheet range

[Richard Schollar]

I created a custom list to sort a range on exiting (deactivating) a worksheet and it worked exactly as intended if I did the sort manually (ie no VBA). The moment I tried plonking it in the Worksheet_Deactivate event, it failed to work and simply sorted the list alphabetically (eg as if the custom list wasn't present/being use). Is this a known problem?

Data to sort looks like this:

<b>Excel 2002</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">43</td><td style=";">S&P Rating</td><td style=";">RTG_SP</td><td style="text-align: center;;">FI</td><td style="text-align: center;;">BDP</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">44</td><td style=";">S&P Rating Effective Date</td><td style=";">SP_EFF_DT</td><td style="text-align: center;;">FI</td><td style="text-align: center;;">BDP</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">45</td><td style=";">Sink Schedule</td><td style=";">SINK_SCHEDULE</td><td style="text-align: center;;">FI</td><td style="text-align: center;;">BDP</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">46</td><td style=";">Sink Schedule With No Factor</td><td style=";">SINK_SCHEDULE_WITH_NO_FACTOR</td><td style="text-align: center;;">FI</td><td style="text-align: center;;">BDP</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">47</td><td style=";">Sinking Fund Factor</td><td style=";">SINKING_FUND_FACTOR</td><td style="text-align: center;;">FI</td><td style="text-align: center;;">BDP</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">48</td><td style=";">Amount Issued</td><td style=";">AMT_ISSUED</td><td style="text-align: center;;">Both</td><td style="text-align: center;;">BDP</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">49</td><td style=";">Amount Outstanding</td><td style=";">AMT_OUTSTANDING</td><td style="text-align: center;;">Both</td><td style="text-align: center;;">BDP</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">50</td><td style=";">Bloomberg Issuer Rating</td><td style=";">RTG_BB_ISSUER</td><td style="text-align: center;;">Both</td><td style="text-align: center;;">BDP</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">51</td><td style=";">Fitch Issuer Rating</td><td style=";">RTG_FITCH_ISSUER_RATING</td><td style="text-align: center;;">Both</td><td style="text-align: center;;">BDP</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">52</td><td style=";">Fitch Issuer Rating Effective Date</td><td style=";">RTG_FITCH_ISSUER_EFF_DT</td><td style="text-align: center;;">Both</td><td style="text-align: center;;">BDP</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">53</td><td style=";">Moody's Issuer Rating</td><td style=";">RTG_MDY_ISSUER</td><td style="text-align: center;;">Both</td><td style="text-align: center;;">BDP</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">54</td><td style=";">Moody's Issuer Rating Date</td><td style=";">RTG_MDY_ISSUER_RTG_DT</td><td style="text-align: center;;">Both</td><td style="text-align: center;;">BDP</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">55</td><td style=";">Pricing Source</td><td style=";">PRICING_SOURCE</td><td style="text-align: center;;">Both</td><td style="text-align: center;;">BDP</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">56</td><td style=";">S&P Issuer Rating</td><td style=";">RTG_SP_ISSUER_RATING</td><td style="text-align: center;;">Both</td><td style="text-align: center;;">BDP</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">57</td><td style=";">S&P Issuer Rating Effective Date</td><td style=";">RTG_SP_ISSUER_EFF_DT</td><td style="text-align: center;;">Both</td><td style="text-align: center;;">BDP</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">58</td><td style=";">Adjusted Price/Earnings ratio</td><td style=";">INDX_ADJ_PE</td><td style="text-align: center;;">Equity</td><td style="text-align: center;;">BDP</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">59</td><td style=";">Beta</td><td style=";">EQY_BETA</td><td style="text-align: center;;">Equity</td><td style="text-align: center;;">BDP</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">60</td><td style=";">Company Has Multiple Shares Indicator</td><td style=";">MULTIPLE_SHARE</td><td style="text-align: center;;">Equity</td><td style="text-align: center;;">BDP</td><td style="text-align: center;;">1</td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Lookups</p><br /><br />

Sorting by column I first then by column H and finally by column G. Custom list was "FI, Both, Equity" and shoud sort col G as above (so should have sorted in the order it appears above). Code to achieve this as follows:

Private Sub Worksheet_Deactivate()
Dim lngLastRow As Long, lngListNo As Long
lngLastRow = Cells(Rows.Count, 1).End(xlUp).Row
On Error Resume Next
Names("AssetCat").Delete
On Error GoTo 0
Range("A2:C" & lngLastRow).Name = "AssetCat"

'Bloomberg table:
lngLastRow = Cells(Rows.Count, "F").End(xlUp).Row
'create custome list (doesn't do anything if list already exists):
Application.AddCustomList listvalues:=Array("FI", "Both", "Equity")
'get custom list number to use in Sort:
lngListNo = Application.GetCustomListNum(Array("FI", "Both", "Equity"))
'both the above work correctly - list created and correct number returned

On Error Resume Next
Names("BB_FLDS").Delete
On Error GoTo 0
Range("E2:I" & lngLastRow).Name = "BB_BDP"
Range("BB_FLDS").Sort key1:=Range("H2"), order1:=xlAscending, key2:=Range("I2"), _
    order2:=xlAscending, key3:=Range("G2"), order3:=xlAscending, header:=xlNo, ordercustom:=lngListNo
 
End Sub

I did wonder if it failed to work because I also have two columns that do not use the custom list and therefore cannot be sorted using the custom list (I and H)???

In the end I simply renamed FI in my data to Bond (in which case standard Excel alphabetic sorting worked as I required).

 

[Richard Schollar]

Good point. How's it look now?

 

[RoryA]

Much better - ta.