VBA Userform Issue - Command Button / msg Box

**teeks** · 01-07-2010, 08:19 AM

I have a command button that when I click drops the contents of numerous list boxes into excel. I wanted to add a message box to tell the user when they have not selected an item in one of the list boxes. They need to select at least one item in each list box. Example below. It is not working, even if I select an item in each list box I still get the "Please select one question!" message box displayed rather than a successful submission. I tried and if then else statement but it did the same. need it to submit the data if items selected in all list boxes, message box if not all items selected. Any help much appreciated. Code i'm trying is;

Please Login or Register  to view this content.

**Andy Pope** · 01-07-2010, 09:42 AM

The problem is you are testing for False on selected items. But the selected property is a array of True/False elements, one for each item. So even if one is selected all the others will be false.

This examples uses a function to determine whether an item has been selected or not.

Please Login or Register  to view this content.

**Andy Pope** · 01-07-2010, 09:43 AM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

Forgot to include this. Can you edit your post please.

**teeks** · 01-07-2010, 10:29 AM

Thanks for the code, how would it look for 2 list boxes on the userform, both of which have to have an item selected and both of which are submitting data to excel at the time of user form command button click

**Andy Pope** · 01-07-2010, 10:35 AM

Thanks for the code tag edit.

You can use the same function. But we need to change the test logic.

Please Login or Register  to view this content.

**teeks** · 01-07-2010, 10:57 AM

I'm getting in a right pickle with this now. I thought i'd post the full messy code. I'm getting the same problem again that the message box keeps reappearing. Once a MsgBox "Please select..." appears I want to return to the user form. Bitten off far more than I can chew, thanks for the help so far Andy!!

Please Login or Register  to view this content.

**Andy Pope** · 01-07-2010, 11:07 AM

I can not see a reason for getting msgboxes other than if no items are selected for a particular listbox.

That said I also don't see in your code where the specific List items is obtained.

So for example if Listbox1 has the last item selected your code will output what exactly? What is lItem

Please Login or Register  to view this content.

**teeks** · 01-07-2010, 11:22 AM

(litem) was a left over from the original code.

I've now got the following code, based on your advice, it tells me ethnicity (listbox4) is not selected even when a option from the list box is Arrrghhhh!! Surprise, surprise, I can't understand why!

Any thoughts. Thanks for all your help so far!

Please Login or Register  to view this content.

**Andy Pope** · 01-07-2010, 11:37 AM

Hard to say without actually having the workbook.

Sure listbox4 is what you think it is?
Maybe the selected item is not visible, are all items visible or can you scroll the list?