Add Check box to cel and Export to Access

[Steven547]

Creating a form for people to record the test information. I was doing it in Acces, but not everyone has Access on their machines.
How would I create a form in excel, similar to access, where I can have a check box (yes/no)...I know you can add a control, but when I export/import it to Access, I want that "response" to be associated with the correct question. Make sense?

 

[kjv1611]

Can't you have the checkbox enter a 1 or 0 in the associated cell in the spreadsheet, and then import those values into a boolean field in Access?

--

"If to err is human, then I must be some kind of human!" -Me

 

[Steven547]

I guess I'm confused because If I add a checkbox, it isn't associated with a particular cel in excel....

 

[Steven547]

Ok...I could do a check box and if it is marked Yes, then make the cel = yes..etc..right? But how would I do that in VBA code for that control to reference a particular cel?

----if me.optionbutton1.value = 1 then (celA3) = Yes

How would I write that above?

 

[kjv1611]

You set the associated cell in the field "ControlSource" in the VBA editor.

To access it, on your user form, right-click on the checkbox, and choose properties. The controlsource field should be about 7 or 8 blocks down from the top.

Though, I'm not sure I remember for sure on how to make it change for each record. You may have to do that via code.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

And actually, this looks like a pretty good short tutorial that would cover everything you'd need. I didn't read the entire thing, but from a quick glance, it looks like it'll get you going:

http://www.contextures.com/xlUserForm01.html

By the way, I did test the controlsource field myself. I created a check box, and set the ControlSource to A1. So, if the checkbox was checked, it entered TRUE in cell A1 on the active worksheet.

Also, if you want that form to be available to more than just one workbook, I'd suggest putting it in each person's Personal Macro Workbook if possible... or else perhaps you can put it in a template file that all future files needing the form will be created.

--

"If to err is human, then I must be some kind of human!" -Me