Bacon Bits:

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.  

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.