+ Reply to Thread
Results 1 to 11 of 11

Translate a simple code to work on a Userform

  1. #1
    Forum Contributor
    Join Date
    11-21-2017
    Location
    USA
    MS-Off Ver
    2016
    Posts
    150

    Smile Translate a simple code to work on a Userform

    I put together a mini-search engine that works great. How it works is that it looks at the input provided by the user in cell "A3" on the "list" tab and then searches for a match in column A of the "Data" tab. All matches are then generated on the "List" tab beginning on row 11. To demonstrate input "blr", for example, in cell A3 on the "list tab". A list of matches should generate below. The code works as follows:

    Please Login or Register  to view this content.
    While the code works great on the worksheet, I can't seem to make it work with a userform. To see the userform I put together, click the button called "Userform". When the userform appears you will see that it contains a list box and a search bar - the same concept as the worksheet. That is, when the user inputs data in the search bar a list of matches should generate in the listbox in the same way as you see on the worksheet.

    The user should then be able to select from one of the items listed and add it to "C11" using the "+" button for however many items needed. Any help would be appreciated.
    Attached Files Attached Files

  2. #2
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Translate a simple code to work on a Userform

    Place the following codes on UserForm Module.
    I renamed the two command buttons to cmdAdd and cmdRemove.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  3. #3
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Translate a simple code to work on a Userform

    Hi, I understood, will try and see if I can help.
    BTW, don't use your alias in The Netherlands or any Dutch speaking country.
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  4. #4
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Translate a simple code to work on a Userform

    Okay, try this, I don't know what you wanted with the - command button.
    Just open the userform and enter search criteria in the search box, the listbox will populate as you type
    Select one or more and press the +

    Hope this is what you were looking for
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    11-21-2017
    Location
    USA
    MS-Off Ver
    2016
    Posts
    150

    Re: Translate a simple code to work on a Userform

    ...Wow. Keebellah, that was amazing. You added a functionality that well exceeded my expectations. I was going to design it such that the user would have to hit the + command button for each line they would like to add. The checkbox you placed next to each listed item allows the user to simply check everything at once before adding it to the list. I look forward to playing around with that. Thank you.

    As far as my alias - Neuk - it sounds like you picked up on the country from where it came from. I can't say I know too much about the country though... Sorry for not being clear about the - command button. Based on the way you designed it, would it be possible to remove an item that was already listed? For example, if the user accidentally selected an item and hit the + command button, could the user then reselect the item they wanted to remove and hit the - command button to remove it from whatever row it may be on? It wouldn't be deleting the row, only clearing the contents from the cell. If not, don't worry about it. What you already helped me on here more than does the trick. Thanks once again.

  6. #6
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Translate a simple code to work on a Userform

    The checkbox in the Listbox is a builtin option, if you set the listbox multiselect you can also choose the type of selection option, checkout the listbox properties when in form design.
    The - could work but then I would add the option add or remove items, if you choose to remove the listbox should be populated with the data already selected, otherwise the other way.
    You can also add code to avoid duplication.
    VBA is very versatile, you only have to tell it what to do.

  7. #7
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Translate a simple code to work on a Userform

    I modified it so you can add, remove play around with
    Attached Files Attached Files

  8. #8
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Translate a simple code to work on a Userform

    The only code missing is to skip the items in new selection that have already been selected

  9. #9
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Translate a simple code to work on a Userform

    Okay, code added
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    11-21-2017
    Location
    USA
    MS-Off Ver
    2016
    Posts
    150

    Re: Translate a simple code to work on a Userform

    Keebellah,

    Very impressive. I never would have conceived it could be done the way you demonstrated. I look forward to studying your work.

    As I study your work, I imagine I will have a few questions. Do you mind if I address those questions to you? I'll be careful not to waste your time, and try and do my own research first.

    Thanks once again for your time.

  11. #11
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Translate a simple code to work on a Userform

    No problem, I'm open for questions, just ask, I don't consider helping others a waste of time, I had to learn too and am still learning (old dog) new tricks

+ 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. Replies: 0
    Last Post: 02-19-2018, 08:54 AM
  2. [SOLVED] Simple MSGBOX which can work with any existing code
    By omega0010 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-23-2013, 05:18 AM
  3. [SOLVED] I need a simple code for a click event using a UserForm ComboBox
    By Pyro Form in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-17-2012, 07:58 AM
  4. translate work schedule to chart counting hours
    By wilsonta in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-11-2011, 10:37 AM
  5. This simple 10 line code doesn't work if just the reference is changed..why?
    By duugg in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-10-2009, 02:28 PM
  6. [SOLVED] Could someone HELP TRANSLATE this simple psuedo code into MACRO pl
    By bxc2739 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-27-2006, 02:46 PM
  7. Replies: 4
    Last Post: 04-27-2006, 02:09 PM

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