+ Reply to Thread
Results 1 to 9 of 9

Get the name of a listbox where an item is selected

  1. #1
    Registered User
    Join Date
    07-24-2015
    Location
    Toledo, OH
    MS-Off Ver
    Microsoft Office 2010
    Posts
    85

    Get the name of a listbox where an item is selected

    Hi all,

    I have a userform with about a hundred list boxes on it. I want a macro that will determine if a list box item has been selected, and if it has, that will return the name of that particular list box, NOT the value of the selected item. For example, if the user has selected the item "Twenty" in a list box that is called "UserList1", then I want the program to first make sure an item is selected, and then return the name "UserList1." Thoughts?

  2. #2
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Get the name of a listbox where an item is selected

    You can do this with class module.
    Add a class module and put this code in it

    Please Login or Register  to view this content.
    Then add this code to your existing user form code
    Please Login or Register  to view this content.
    The sub SomeListBox_Click will run every time any list box is clicked.
    Note that the variable SomeListBox refers to a user-defined Class1 object, to refer to the listbox that was clicked, use SomeListBox.ListBox.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  3. #3
    Registered User
    Join Date
    07-24-2015
    Location
    Toledo, OH
    MS-Off Ver
    Microsoft Office 2010
    Posts
    85

    Re: Get the name of a listbox where an item is selected

    Okay, but rather than having a message box pop up with the listbox name, is there a way to set that listbox as a variable so I can do things to it? Something like this, only when I tried this it gave me a type mismatch:

    Please Login or Register  to view this content.

  4. #4
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Get the name of a listbox where an item is selected

    A userform listbox is type msForms.ListBox. I'm not sure but I think that ListBox is a depreciated type for use by Form's control ListBoxes. (Similar to the Button data type)

    Try
    Please Login or Register  to view this content.
    I notice that LB is scoped only to that procedure.

  5. #5
    Registered User
    Join Date
    07-24-2015
    Location
    Toledo, OH
    MS-Off Ver
    Microsoft Office 2010
    Posts
    85

    Re: Get the name of a listbox where an item is selected

    Okay, that got rid of the error, but now I can't figure out how to use that variable the way I need.

    I should have explained more from the beginning. Here's what I want to do: The user has the ability to add items to the various listboxes on this userform. I want the user to be able to select a listbox item and click a button that will remove that item from the listbox. Since there are about a hundred possible listboxes, let alone the dynamic number of items within each one, I need a macro that will figure out whether or not a listbox item is selected in the first place, then figure out which listbox it's in, and then remove the item from that listbox.

    I thought if I could figure out how to identify the listbox with the selected item, I could then remove the selected item easily enough. I know the syntax for that. I just can't figure out how to apply this class module thing to enable me to do this on a button click. I tried calling the SomeListBox_Click macro from the button click macro, and that definitely did not work.

  6. #6
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    995

    Re: Get the name of a listbox where an item is selected

    Hi, Jerbinator
    I assume the listbox are set to multi select.
    So maybe something like this:
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    07-24-2015
    Location
    Toledo, OH
    MS-Off Ver
    Microsoft Office 2010
    Posts
    85

    Re: Get the name of a listbox where an item is selected

    Quote Originally Posted by Akuini View Post
    Hi, Jerbinator
    I assume the listbox are set to multi select.
    So maybe something like this:
    Please Login or Register  to view this content.

    That worked great. Thank you!

  8. #8
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    995

    Re: Get the name of a listbox where an item is selected

    Quote Originally Posted by Jerbinator View Post
    That worked great. Thank you!
    You're welcome & thanks for the reply

  9. #9
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Get the name of a listbox where an item is selected

    Without using looping through controls or using class modules you could:
    1) Set the TakeFocusOnClick of the command button to FALSE.
    2) use this code to drive the command button

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Listbox item Highlighted but not Selected
    By shawnvw in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-17-2015, 05:29 PM
  2. Selected item in first listbox.
    By waka in forum Access Tables & Databases
    Replies: 1
    Last Post: 02-09-2015, 11:45 PM
  3. [SOLVED] Selected Listbox Item to Textbox
    By TheRobsterUK in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-17-2014, 04:07 PM
  4. [SOLVED] Macro to run when item is selected from listbox
    By kosherboy in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-30-2014, 09:58 PM
  5. [SOLVED] Run 1 macro when any item from listbox is selected
    By kosherboy in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-30-2014, 09:52 PM
  6. Remove selected item from listbox
    By ChrisMattock in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-28-2014, 08:36 AM
  7. How to copy selected listbox item to sheet
    By michdan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-14-2005, 08:05 AM

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