Closed Thread
Results 1 to 14 of 14

Delete a Item from a Listbox.

  1. #1
    abhay_547
    Guest

    Delete a Item from a Listbox.

    Hi All,

    I have the below code which deletes all items from a listbox and my excel sheet which is the source for populating that listbox. I am using a option button style for my listbox and the selection style as single .i.e. you can select only one item at one go in the lisbox. I want my macro to delete the selected item from my worksheet .i.e. it's entire row so that it doesn't reflect in my lisbox any more. Below is my code :

    Please Login or Register  to view this content.
    Thanks a lot for your help in advance.

  2. #2
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Delete a Item from a Listbox.

    Please Login or Register  to view this content.



  3. #3
    abhay_547
    Guest

    Re: Delete a Item from a Listbox.

    Hi Snb,

    Thanks a lot for your reply, The code which you have provided in your last post doesn't work properly. It deletes my header row from URL sheet. It doesn't delete the selected Item from the URL sheet. Please help..

    Thanks a lot for your help in advance.

  4. #4
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Delete a Item from a Listbox.

    ListIndex starts at zero, so to get the Row number you need to add 1 plus 1 for the Header Row assuming this is in A1.

    I believe Removeitem only works if the lines have been added using AddItem

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by royUK; 10-18-2010 at 01:32 AM. Reason: attach example
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  5. #5
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Delete a Item from a Listbox.

    Experiment with

    Please Login or Register  to view this content.
    Until you are satisfied.

    The simplest way to populate a listbox is using 'rowsource' if your data are in a sheet.
    Otherwise you should use the property 'List'.
    Only in the case you add 1 item at a time additem is worth considering.

    When using 'rowsource' deleting an item in the 'source' automatically adjusts the listbox. In that case you do 2 thing in 1 move.
    When using .list you have to adjust the 'source' and afterwards the property .List because both are unconnected.

  6. #6
    abhay_547
    Guest

    Re: Delete a Item from a Listbox.

    Hi snb,

    Thanks a lot for your reply, I have tried to incorporate the code which you have provided in your earlier post in my userform and it deletes the selected item from the excel sheet .i.e. URL List but it doesn't get immediately deleted from the Listbox .i.e. I need to reload the userform in order to see my change .i.e. the listbox doesn't get updated immediately.


    Thanks a lot for your help in advance

  7. #7
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Delete a Item from a Listbox.

    Have you bothered to look at the example that i attached?

  8. #8
    abhay_547
    Guest

    Re: Delete a Item from a Listbox.

    Hi Roy

    Thanks a lot for your reply, I tried to incorporate the code which "snb" had provided in his earlier post in my userform and it deletes the selected item from the excel sheet .i.e. URL List but it doesn't get immediately deleted from the Listbox .i.e. I need to reload the userform in order to see my change .i.e. the listbox doesn't get updated immediately.

    One more thing which is creating a problem is suppose I have only row data then my lisbox box gets populated with blank items .i.e. one item and 65534 blank items. Attached is the screenshot for your reference.

    Following is the modified code which I have come up with the code provided by you but even this code is creating the blank row issue. :

    Please Login or Register  to view this content.

    Thanks a lot for your help in advance
    Attached Images Attached Images

  9. #9
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Delete a Item from a Listbox.

    The code that I posted worked

    There's no point posting an image, attach the workbook

  10. #10
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Delete a Item from a Listbox.

    You must be joking:

    Please Login or Register  to view this content.
    See the attached attachment
    NB. if you want to avoid empty items in the listbox remove the hyphen in the doubleclick event.
    Attached Files Attached Files
    Last edited by snb; 10-18-2010 at 05:56 PM.

  11. #11
    abhay_547
    Guest

    Re: Delete a Item from a Listbox.

    Hi Roy,

    Thanks a lot for you reply, You are right that the code which you have provided is working but it still has an issue which I have highlighted in the screenshot which I had attached in my earlier post. The issue is that suppose I have only one row data in my excel sheet then my lisbox box gets populated with one item and 65534 blank items, and I think the issue is because of the below line which i have highlighted in red.

    Please Login or Register  to view this content.
    Thanks a lot for your help in advance.

  12. #12
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Delete a Item from a Listbox.

    Use xlUp as I did
    Please Login or Register  to view this content.

  13. #13
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Delete a Item from a Listbox.

    snb, it would be good if you said who you are referring to in your posts

  14. #14
    abhay_547
    Guest

    Re: Delete a Item from a Listbox.

    Hi Roy / Snb,

    Thanks a lot it's working now. Below is my final code.

    Please Login or Register  to view this content.
    Thanks a lot once again

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1