Preparing text string based upon check box input in a userform

Hello everyone, I have a userform to log contacts. There are several check boxes, which I will call Fred, Barney, Wilma and Betty. It is currently set up to put the info into a row in a spreadsheet as true or false. I process that in my spreadsheet to generate text that reads something like: I spoke to Fred, Wilma, and Betty.

I was wondering if there was an easy way to do it in my VBA Code to just spit out the finished text. It would need to be able to say which of the 4 were contacted, including cases where more than one was involved, insert commas where appropriate, and a period at the end of the sentence. This is part of a much bigger process, but I would appreciate it if you could help me past this initial hurdle.

I am thinking that I am going to have to do some looping, but that is just a guess.

Thank you for any guidance you can give.

BobR

Here is part of my code to plug the info into the row, if it helps:

Please Login or Register  to view this content.

I was able to just string them together with iif, but I really do not want to have to go back and edit grammar.

Please Login or Register  to view this content.

But that will always give me the commas at the end. I am pretty sure I could trim the last comma from the string and slap a period on at the end. Of course an and would be useful if appropriate.

I managed to sort of solve the problem with an extensive if...then code. Sorry for not indenting properly.

Please Login or Register  to view this content.

Is there a way to do the same thing with looping? I tried to do a vlookup that put the me.fred.value , etc. into a variable that could be evaluated as a boolean, but I could not figure out how to get the value that was in the me.fred.value (a check box state) to be the value that was in the variable to be evaluated.

The full project will have several sets of these kinds of check box groups (what we talked about, comments that I use frequently, etc.), so I am trying to make it as short and efficient as possible. I would also like end users to have a "settings" page, where they can easily change the fred to whatever they want, so I really would like to try to keep the vlookup.

I managed to figure it out. I had to take the check boxes from my form, output it into a spreadsheet, and then pull the information from the spreadsheet back into the macro, but it worked like a champ. The looping also took me from a total of 135 lines of code down to 51 (and I think some of those are going to go away too). Here is the important part to make it create the sentence properly:

Please Login or Register  to view this content.