Using Multiple Combo Box Values to populate a cell (Excluding blank combo boxes)

Hi, I am using Excel '07 and have a user form which allows the user to list people present divided into 2 groups. Currently I have 10 combo boxes for staff, as well as an additional 10 combo boxes for others. Each grouping of combo boxes is populated by using a dynamic range referencing each category (probably TMI but you never know). The goal is to have all of the staff listed in one cell on the worksheet (cells(3, "J")) and all of the other people in (cells(3,"K")). I would like each person to be separated by a comma and the last to have an Ampersand before it [Staff1, Staff2, .... Staff9, & Staff10]. Getting this to work with all 10 combo boxes is not an issue, but I would like to have it work without unnecessary punctuation if say only 4 list boxes are used. Results should appear as [Staff1, Staff2, Staff3, & Staff4], but currently are [Staff1, Staff2, Staff3, Staff4, , , , , , &].

I have tried to get this with 2 different (staff & other) IF and ElseIF statements which I believe to be the issue as it seems in both instances, it is accepting the first IF.

ComboBox Properties:
All except the first (eg. CB_Staff1) have .visible = False as default. If the Previous ComboBox is populated, then code will switch to .visible = True. All ComboBoxes have a default .value = [Blank] <--Could this be an issue?

Try 1, Looking at .value

Please Login or Register  to view this content.

Try 2, Looking at .Visible

Please Login or Register  to view this content.

Both attempts are showing the same result [Staff1, Staff2, Staff3, Staff4, , , , , , &] not what I am looking for [Staff1, Staff2, Staff3, & Staff4].

I have been trying to figure this out for 2 hours. Am I going about this the wrong way?

Any help on this matter would be GREATLY appreciated!

Untested, but something like this

Please Login or Register  to view this content.

Wow, that worked great! Thank you! I ended up changing it very slightly to get it going, here is what I had:

Please Login or Register  to view this content.

Any idea why the ElseIf wasn't working? Just out of curiosity to learn.

Originally Posted by mnadamn19

Any idea why the ElseIf wasn't working? Just out of curiosity to learn.

Which part?