none
Listbox Item Clearing is Not Working in VBA Excel RRS feed

  • Question

  • I have a two list boxes with one button when the user can click the button move all the list item from listbox1 to listbox2. its working fine in excel 2007 & 2013 but when the listbox1 is becomes empty app is getting restarted IN EXCEL 2016.

    My Code is

    For i = 1 To ThisWorkbook.Sheets("MultiSheet").ListBoxes(strFromlb).listCount
       ThisWorkbook.Sheets("MultiSheet").ListBoxes(strTolb).AddItem ThisWorkbook.Sheets("MultiSheet").ListBoxes(strFromlb).List(1)
       ThisWorkbook.Sheets("MultiSheet").ListBoxes(strFromlb).RemoveItem (1)
    Next i

    Here strFromLb is clearing the values but when it clearing last value my VBA App is excel has been restarted.

    Then I have tried code to clear the listbox

    ThisWorkbook.Sheets("MultiSheet").ListBoxes(strFromlb).ControlFormat.RemoveAllItems
    ThisWorkbook.Sheets("MultiSheet").ListBoxes(strFromlb).Items.Clear

    The error is

    "Object doesnt supported to property or method"

    Then

    ThisWorkbook.Sheets("MultiSheet").ListBoxes(strFromlb).Clear

    This code I got the 400 error. so kindly help me.

    Friday, November 18, 2016 3:02 AM

All replies

  • Re: listbox error

    Try replacing all instances of "ListBoxes" with "ListBox".
    As far as I am aware, "ListBoxes" does not exist in VBA.

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    Friday, November 18, 2016 5:07 AM
  • Hi,

    I see couple of problems in your code:

    1. Looking at your code, it seems you are using FormControl Type List Box and not Activex ListBox. Syntaxes and also methods to access the list or clear the list will differ based on the type of ListBox you are using.

    2. I hope strFromlb and strTolb are the variables where name of the listboxes are stored. If not and these are the actual names of the control boxes then you should put them in to double quotes..like this ....ListBoxes("strFromlb")

    3. To remove all the items means to clear all the list from a List Box (FormControl ListBox) you should use following statement:

    Worksheets("MultiSheet").ListBoxes("strFromlb").RemoveAllItems

    4. Finally based on my understanding here is the code which will do you job perfectly. You do not need to use for loop to do so:

    With ThisWorkbook.Sheets("MultiSheet")
        .ListBoxes("strTolb").RemoveAllItems 'to clear all the existing items from strTolb ListBox before copying the list from firstListBox
        .ListBoxes("strTolb").List = .ListBoxes("strFromlb").List ' Assign the list
        .ListBoxes("strFromlb").RemoveAllItems ' Now remove all items from the first listbox
    End With


    Vish Mishra

    Friday, November 18, 2016 5:21 AM
  • Hi Jim,

    ListBoxes is a hidden member, like Buttons, Rectangles, etc

    With this set of collection type objects, apart from referring to individual objects by name or index, with most you can also apply the same property or method to the entire collection in one go, eg the method Vish suggested for the OP

    ActiveSheet.ListBoxes.RemoveAllItems

    Monday, November 21, 2016 10:58 PM
    Moderator
  • Hi Peter,
    Re: Listboxes

    In the middle of the night after posting, I woke up and went back to the computer and right-clicked in the object browser (in xl2003) and found the list of hidden items.  I had completely forgotten about them.
    It still makes no sense why MS replaced them.

    Thanks for the alert; wish you would post more often. 
    Also wish MS had provided VBA access to the Ribbon and that fried steak and ice cream were good for you. <grin>

    Regards,
    Jim Cone

    Monday, November 21, 2016 11:30 PM
  • I have tried it but it doesn't worked out . it saying error was "Object doesnt support for this property or method". 
    Tuesday, November 22, 2016 2:45 AM
  • Ya i have tried this one also but when it executing this line itself getting restarted the application
    Worksheets("MultiSheet").ListBoxes("strFromlb").RemoveAllItems
    Tuesday, November 22, 2016 2:49 AM
  • Re:  I have tried it

    My recommendation was wrong. Please ignore it.
    '---
    Jim Cone


    Tuesday, November 22, 2016 2:49 AM
  • NO, This one is also not working the problem is that it clear all list of values in listbox but when the listbox is becomes empty at the time is getting crashed and restarted.
    Tuesday, November 22, 2016 2:57 AM
  • What hidden items in my object explore is showing any hidden item in my worksheet 
    Tuesday, November 22, 2016 2:59 AM

  • The hidden item I saw was "Listboxes".  It still can be used, but MS does not show it in the help file in order to discourge use.

    • Edited by James Cone Tuesday, November 22, 2016 7:20 AM
    Tuesday, November 22, 2016 7:19 AM
  • No there is no any hidden listboxes in worksheet. i have checked it. see my code is working but when we remove last item from the listboxes at that time getting restarted.
    Wednesday, November 23, 2016 2:26 AM