Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Feb 2003
    Location
    Alabama, USA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Custom list weirdness (Excel97/2000, VBA5/6)

    While developing an add-in for Excel that utilizes custom lists, I noticed some odd behavior - only the first four lists appeared in my UserForm. Upon further inspection, I determined that this behavior occurred only when 1) Excel was initially launched and 2) I first displayed the UserForm or 3) I displayed the "Options" dialog or 3) I assigned one or more of the lists to an array variable (my current workaround). Otherwise, all's apparently well. I've tested this on Excel 97 and 2000, on my home and work machines and several co-workers' machines, and all displayed the same behavior. Assuming that custom lists have been added, the simplest way to demonstrate this is to run "debug.Print Application.CustomListCount" in the Immediate window immediately after launching Excel, switch back to Excel, open and close the "Options" dialog, and run the command in the Immediate window again. Doing this on my home computer with one custom list added, my first result was 4 and my second was 5. I've searched the net (Microsoft.com included) and found nothing. Have I stumbled on something here, or is this supposed bug old news?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Custom list weirdness (Excel97/2000, VBA5/6)

    I can confirm your observations (Excel 97 SR2, Dutch):

    1) CustomListCount doesn't get initialized correctly. Initially, it always returns 4, the number of built-in lists
    2) There is no mention of this bug in the Knowledge Base.

    Referring to a custom list (even a built-in one) is enough to force a correct count: assigning a list to an array variable (as you mentioned), or even assigning one element to a string variable: strElement = Application.GetCustomListContents(1)(1).

  3. #3
    New Lounger
    Join Date
    Feb 2003
    Location
    Alabama, USA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom list weirdness (Excel97/2000, VBA5/6)

    Nice to know that, at the least, the workaround isn't very involved. Thanks for your reply, Hans.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •