+ Reply to Thread
Results 1 to 7 of 7

Four MultiSelect Listboxes and TextBoxes: Advise No Selection/Entry Made, Return to Form

  1. #1
    Registered User
    Join Date
    07-15-2013
    Location
    Arizona
    MS-Off Ver
    Excel 2016
    Posts
    64

    Four MultiSelect Listboxes and TextBoxes: Advise No Selection/Entry Made, Return to Form

    Hello Forum,

    Thank you in advance.

    I have an ActiveX userform that has a single frame with 4 multiselect listboxes, 4 textboxes, update command button to send info to worksheet, command buttons to close or refresh the form. If the user does NOT make any selections from the 4 multiselect listboxes OR does NOT enter anything in one of the 4 textboxes, they should receive a message alert when they click the update command button that is coded to send entries to the target Excel worksheet. That's a problem if they send blank entries and it overrides any current entries that exist in the worksheet.

    So the flow of the code should be something like this when they click the command button (maybe the last steps are reversed?):
    1. Validation: check to see if there are ANY selections in ANY multiselect listbox OR entries in ANY textbox within the frame (or userform)
    2. If result is FALSE, pop a message box stating something like "No entries made, return to the form and make entries or use the X button to close form with no changes" and have the code stop there
    3. If condition is TRUE, then continue with the code I have to send the selections to the target cells in the worksheet.

    I've searched all day and posted this thread earlier this morning with a different approach that is not as clean by disabling the update command button (didn't get a completely working solution there either). It feels as though my stumbling block is having multiple multiselect listboxes. I've seen many posts for checking for selections for 1, but not more than that.

    Any suggestions as always are much appreciated!

    Thanks,
    Chris

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,626

    Re: Four MultiSelect Listboxes and TextBoxes: Advise No Selection/Entry Made, Return to Fo

    Chris

    The only way to check nothing is selected in a multiselect listbox is to loop through each item and see if it's selected.

    You could use a function like this to check if selections have been made in a listbox.
    Please Login or Register  to view this content.
    To check all the listboxes and textboxes on a form you could use something like this.
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    07-15-2013
    Location
    Arizona
    MS-Off Ver
    Excel 2016
    Posts
    64

    Re: Four MultiSelect Listboxes and TextBoxes: Advise No Selection/Entry Made, Return to Fo

    Thanks for the quick reply Norie! I'll give this a shot and reply with my results. I tried to cobble some other posts together to use Case but couldn't get it to work. Back to my workbook!

    Chris

  4. #4
    Registered User
    Join Date
    07-15-2013
    Location
    Arizona
    MS-Off Ver
    Excel 2016
    Posts
    64

    Re: Four MultiSelect Listboxes and TextBoxes: Advise No Selection/Entry Made, Return to Fo

    Hello Norie,

    I'm getting a "Compile error: Sub or Function not defined" message at "IsSelected(ctl)" for "Case "ListBox"", the editor highlights the "IsSelected" section. I'm rather a novice at VBA so I'm not sure if I'm supposed to augment that section (or the similar section for "Case "TextBox""). I took your code and pieced it with my rudimentary code to move selections to target cell in the worksheet. Can you take a look at the error I'm getting as stated above and check if how I've pieced my code with yours to see if the syntax is good?

    Please Login or Register  to view this content.

    I've also attached a quick sample workbook with the form (note my red X and yellow refresh buttons aren't coded) where the "Enter Primary Audiences" button on the worksheet launches the form.

    Again, many thanks in advance for any help/direction! Chris
    Attached Files Attached Files

  5. #5
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,626

    Re: Four MultiSelect Listboxes and TextBoxes: Advise No Selection/Entry Made, Return to Fo

    Did you not add the function IsSelected I posted?

    It's the first piece of code in my post.

  6. #6
    Registered User
    Join Date
    07-15-2013
    Location
    Arizona
    MS-Off Ver
    Excel 2016
    Posts
    64

    Re: Four MultiSelect Listboxes and TextBoxes: Advise No Selection/Entry Made, Return to Fo

    Oh Norie, I did not! But now I did! Who said ignorance is bliss?! In my case with VB it's a grinding, grand learning experience. I thought the first part was just instruction for a single multiselect count kind of as an FYI, didn't put it together with the second part.

    After adding that first part, the code works as I wanted it to do! I've used your posts before Norie and just wanted to compliment you on having users like me to think about their solution as opposed to just providing the answer (even though you pretty much did that for me here!) so we can learn. Thanks for sharing your expertise here! This is an awesome forum.

    Best regards,
    Chris


    My final code with explanations for other novices like me who might find this (more importantly, Norie's coding section) helpful:

    Please Login or Register  to view this content.

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,626

    Re: Four MultiSelect Listboxes and TextBoxes: Advise No Selection/Entry Made, Return to Fo

    Chris

    No problem, glad it worked.

+ 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. Enable Command Button if ANY of 4 Listboxes Have a Selection Made
    By excelforumkeys in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-12-2014, 02:18 PM
  2. Replies: 0
    Last Post: 06-26-2012, 03:51 PM
  3. Transfer Selections of Multicolumn Multiselect Listboxes to Cells
    By win_win in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-22-2012, 12:12 AM
  4. How do I get my Listbox to return the selection made?
    By imaquila in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 02-14-2012, 06:21 PM
  5. Clearing multiple textBoxes and listboxes
    By grahammal in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-09-2007, 06:31 AM

Tags for this Thread

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