Need help with a form containing radio buttons, drop downs & check boxes

[texaschai]

Hello Excel friends,
I have a form I've created in Excel that has multiple questions. Each question either has a set of answers in the form of either a drop-down (using data validation), check-boxes where one or more options can be selected (using Developer) or radio buttons (using Developer and grouping them so that each question has a separate group of answers to choose from).
The help I need is in three different areas:

1. Is there a way to make the radio buttons "uncheck-able" by the user so that when they click a radio button twice, it goes blank (like a check box does, only I actually need it to be a radio button so that only one answer is selected)? These aren’t savvy Excel users, so asking them to use Developer properties to mark it False is asking too much.

2. Is there a way to make certain questions required so that when the user goes to save the form, it says which answers are still necessary in order to move forward? I know there is a VBA for this for certain cells to have data, but what if the answers are in radio buttons, drop downs or check marks? For the radio buttons, one button from each group would need to be checked to proceed; for the drop downs, anything but the first option (which says “CHOOSE DROP DOWN”) would need to be selected to proceed; and for the checkboxes, at least one or more boxes would have to be checked in order to proceed. Is there a way of easily doing this or should I just scrap the whole idea and move to a fillable PDF form or something?

3. How do I get the radio button and check box selections to feed to another tab’s form based on which radio button is chosen? I want the answers to change the way another tab’s cells operate.

Thanks for all your help.
Charlotte

 

[tonyyy]

Hello Excel friends,
I have a form I've created in Excel that has multiple questions. Each question either has a set of answers in the form of either a drop-down (using data validation), check-boxes where one or more options can be selected (using Developer) or radio buttons (using Developer and grouping them so that each question has a separate group of answers to choose from).
The help I need is in three different areas:

1. Is there a way to make the radio buttons "uncheck-able" by the user so that when they click a radio button twice, it goes blank (like a check box does, only I actually need it to be a radio button so that only one answer is selected)? These aren’t savvy Excel users, so asking them to use Developer properties to mark it False is asking too much.

2. Is there a way to make certain questions required so that when the user goes to save the form, it says which answers are still necessary in order to move forward? I know there is a VBA for this for certain cells to have data, but what if the answers are in radio buttons, drop downs or check marks? For the radio buttons, one button from each group would need to be checked to proceed; for the drop downs, anything but the first option (which says “CHOOSE DROP DOWN”) would need to be selected to proceed; and for the checkboxes, at least one or more boxes would have to be checked in order to proceed. Is there a way of easily doing this or should I just scrap the whole idea and move to a fillable PDF form or something?

3. How do I get the radio button and check box selections to feed to another tab’s form based on which radio button is chosen? I want the answers to change the way another tab’s cells operate.

Thanks for all your help.
Charlotte

Hi Charlotte,

1. It's my understanding that once activated, the only way to deactivate a button is to activate another. My suggestion is to add another radio button with a caption such as "None of the above."
2. Here's some code that checks if selections have been made in two combo boxes:

If cboFirst.Value = "" Or cboSurname.Value = "" Then
    MsgBox "Please make selections from the First and Last Name drop down menus."
    Exit Sub
End If

This can be adapted to handle radio buttons, check boxes and other form controls.
3. Haven't worked with tabs so I can't help you there.

Good luck!

tonyyy

 

[texaschai]

Hi Tonyyy,
Thank you so much! I fixed the problem with #1, haven't tried your code with #2 yet (do I do that in the worksheet or what?), and with #3, I answered my own question (I shouldn't have said tabs, but 'other worksheets in the workbook'). I went ahead and assigned a cell to the radio button and then hid the row where that assigned cell is so that the answer (true/false) can then be used in a formula on another sheet.

I have another question - maybe you can answer or someone else:
When I protect the form (password protect it), I can say that the unlocked cells are modify-able, but how do I do the same for the radio buttons? I'd ideally like the user to tab through the form an skip all the cells that are blank. Any thoughts/suggestions would be greatly appreciated!

Thanks again!!

 

[tonyyy]

Hi Charlotte,

I suppose I should take a step back and ask if you're developing a UserForm. A UserForm is created by opening the Visual Basic Editor and inserting a UserForm, then using the Toolbox to insert Form Controls.

Or are you using a worksheet and from the Developer menu Inserting Form Controls?

I assumed the first, and that will utilize a different approach than if you're using the second.

 

[texaschai]

uh............
I didn't know the former was an option; I'm doing the latter.
But now I wish I had asked first.
Sounds like I may have taken the long route?

 

[tonyyy]

Implementing UserForms requires the use of vba; inserting Form Controls into a worksheet does not.

That being said, if you want to trigger some action when the workbook is saved - such as a MsgBox displaying "Please answer the required questions." - that too would require vba.

 

[texaschai]

Is UserForms already installed in regular Excel or is it an Add On? I've never heard of it (not saying much) but I have used VBA a little bit.

 

[tonyyy]

There are two good tutorials on Form Controls and UserForms on the web (I'm sure there are more, but I've used these 2.)

[h=1]Form Controls – Adding Interactivity to Your Worksheets[/h]
[h=1]Create an Excel UserForm[/h]

 

[sous2817]

It's part of the Excel / Office install. Have a look here to get you started.

 

[texaschai]

OMG I feel like my whole world just shifted, the clouds parted and the angels are singing. Ha ha! I consider myself pretty far on the intermediate side of Excel and have even taught classes in shortcuts and pivot tables, but the whole VBA realm is an area that's like an attic I've tried to avoid. Now you're telling me there's gold up there! Ha ha. Anyway, thank you. I'll take a looksee.

Charlotte