Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: ComboBox drop-down not clearing out old values

  1. #1
    New Member
    Join Date
    Nov 2015
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default ComboBox drop-down not clearing out old values

    Hi,

    On my Workbook Open code I first clear the combo values then loop through all the pivot items in a pivot field to generate the new list (this is after pivot has refreshed, so items can vary each time).

    However, some old values are still showing up which no longer exist in the pivot. The pivot table is linked to an Access query.

    Can anyone help me to remove the old values?

    Thanks

    Baker

  2. #2
    Board Regular
    Join Date
    May 2011
    Location
    Russia
    Posts
    2,834
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: ComboBox drop-down not clearing out old values

    Did you clear combobox before filling it with values?
    Code:
    ComboBox1.Clear

  3. #3
    New Member
    Join Date
    Nov 2015
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: ComboBox drop-down not clearing out old values

    Quote Originally Posted by Sektor View Post
    Did you clear combobox before filling it with values?
    Code:
    ComboBox1.Clear
    Yes, it clears beforehand. I've stopped the code at that point and the drop-down is blank. It then loops through the pivot table items and the old values are included.

    The old values are definitely not in the query anymore, but must somehow still be saved in the pivot items?? I could probably delete the pivot table and re-build it but this is an automated weekly report so I don't want the manual step.

    Thanks for any help on this one.

  4. #4
    Board Regular
    Join Date
    May 2011
    Location
    Russia
    Posts
    2,834
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: ComboBox drop-down not clearing out old values

    Well, when you run your code step-by-step (F8), couldn't you verify, which values go to combobox?

  5. #5
    New Member
    Join Date
    Nov 2015
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: ComboBox drop-down not clearing out old values

    Quote Originally Posted by Sektor View Post
    Well, when you run your code step-by-step (F8), couldn't you verify, which values go to combobox?
    Fair point, I've just done that and stepping through I can see that these old values are still PivotItems in my PivotField but I don't know what to do to prevent it? When I go to the pivot table itself, and drop-down the field in question, the old values are not showing there. But when the code runs they are.

  6. #6
    New Member
    Join Date
    Nov 2015
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: ComboBox drop-down not clearing out old values

    Found this workaround which will do for me: Daily Dose of Excel Blog Archive Looping through PivotItems that Don’t Exist

    Taking the Record Count >0 will do the job for me here. Thanks for your help though Sektor.

  7. #7
    Board Regular
    Join Date
    May 2011
    Location
    Russia
    Posts
    2,834
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: ComboBox drop-down not clearing out old values

    You're welcome!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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