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

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

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

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.