I was fiddling around with Custom Lists the other day, when I noticed that Microsoft prevents me from deleting any of their built-in Custom Lists.
Don't believe me? Just pull up the Custom Lists dialog box and try to delete that lame Sun – Sat list. You can't do it. The Delete button is disabled like this:
Why? I've done real-world work in Excel for over 15 years, and I have never needed Sun-Sat. Even if I did need it, why prevent me from deleting the default lists?
Out of spite, I decided I was going to find a way to delete that dumb-ass list.
So I turned to VBA. First, I tried the DeleteCustomList method. It turns out, you need to know the index number of the Custom List you're deleting.
After a little research, I found out the only way to get the index number of a Custom List, is to literally pass the entire list to the GetCustomListNum method.
Here, you can see that you have to pass the list in an Array.
The list you pass has to be EXACT. If you goof, you get an index number of 0.
This seems a little clunky to say the least. What if my list is 50 strings long?
Now, I have to admit, I can't think of a better way to get the index number. But it still seems weird to pass the entire list.
In any case, this is the code I tried next.
Dim i As Integer
i = Application.GetCustomListNum(Array("Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat"))
Application.DeleteCustomList (i)
End Sub
Guess what - it failed. Apparently, you can't delete the default lists via VBA either. There must be something that tells Excel not to touch the first 4 lists. Bastards! You win this time Microsoft, but I'll be back.
On a positive note, you can Add a Custom List using VBA.
Now, this may seem fairly useless, but I can imagine this coming in handy when you want to share your Custom Lists with your co-workers. For example, imagine you work at McDonalds and you want to share your list with 20 people who all have lame Excel skills.
Simply have them run this code, and Excel will automatically add a new Custom List.
Dim i As Integer
'Check if the list exists.
i = Application.GetCustomListNum(Array("Two All Beef Patties", "Special Sauce", "Lettuce", "Cheese", "Pickles", "Onions", "Sesame Seed Bun"))
'Exit if the list is already there.
If i> 0 Then
Exit Sub
Else
'Add the list if it does not exist.
Application.AddCustomList (Array("Two All Beef Patties", "Special Sauce", "Lettuce", "Cheese", "Pickles", "Onions", "Sesame Seed Bun"))
End If
End Sub
This number must be greater than or equal to 5 (Microsoft Excel has four built-in custom lists that cannot be deleted).
cfr: http://msdn.microsoft.com/en-us/library/bb223327.aspx
It is interesting to note that if you have an Office suite in language X installed on a Windows system in language Y, then you have twice the standard lists and hence the delete number limit is raised to 9!
Mike: All that reading of Walkenbach's power programming with VBA has paid off... I've substantively improved your macro:
Sub Add_Big_Mac()
Dim i As Integer
'Check if the list exists.
i = Application.GetCustomListNum(Array("5 rashers of Streaky Bacon","Two All Beef Patties", "Special Sauce", "Lettuce", "Cheese", "Pickles", "Onions", "Sesame Seed Bun"))
'Exit if the list is already there.
If i> 0 Then
Exit Sub
Else
'Add the list if it does not exist.
Application.AddCustomList (Array("5 Rashers of Streaky Bacon","Two All Beef Patties", "Special Sauce", "Lettuce", "Cheese", "Pickles", "Onions", "Sesame Seed Bun"))
End If
End Sub
@BEL8490
"...Office suite in language X installed on a Windows system in language Y, then you have twice the standard lists..."
Interesting.
Also, it seems the same thing happens if you have two Office Suites (2003 + 2007) installed.
(At least on my Home PC as well as on my Work PC.)