Question about checkboxes and VBA

[XinYoung]

Hello all!

I hope you're all cozy and safe at home.

I'm building a form that has multiple checkboxes in a single cell. Users fill out the form and click an Enter button. Everything they filled out is copied to another worksheet.

#1 - How do I export the checkboxes that were selected to my destination sheet? I want the selections to appear in one cell, separated by commas.
#2 - How can I prevent the Enter button from overwriting/pasting over itself in the destination sheet? I need it to go to the next empty row each time it's clicked.
#3 - When I'm done, I want to protect the worksheets. Even though the cell containing the checkboxes isn't locked, you can't interact with the checkboxes when the sheet is protected. Why is this? Is there a workaround?


An example workbook is attached. The only VBA I have so far is below:

Please Login or Register  to view this content.

Thank you!

[XinYoung]

OK, I figured out #2. Still having issues with the checkboxes though.

Please Login or Register  to view this content.

[LJMetzger]

Hi XinYoung and welcome to ExcelForum,

There are two types of CheckBoxes you can put on the Spreadsheet:
a. 'ActiveX' CheckBoxes which have more capabilities, are harder to work with, and DO NOT PLAY WELL WITH OTHERS. 'ActiveX' CheckBoxes tend to move and or disappear and possibly cause other problems on a whim. They should probably be avoided.
b. 'Forms' CheckBoxes which look ugly but are socially friendly.

Having said that, I used your existing 'ActiveX' CheckBoxes, and added a few 'Forms' CheckBoxes in Cell 'B4' so you could see the difference, and provided code using both. The following code is included in the attached modified copy of your file in Ordinary Code Module ModCheckBoxInterface. I also included code to Protect/Unprotect Sheet 'Form'. The same things can be done manually.

Please Login or Register  to view this content.

To prevent typos from ruining days and weeks of work 'Option Explicit' is NEEDED at the top of each code module. This prevents errors caused by missspellings and FORCES every variable to be DECLARED (e.g. Dim i as Integer). http://www.cpearson.com/excel/DeclaringVariables.aspx

Lewis