Excel VBA ActiveX Series #2 Checkbox, Changing cell value on click, and background color!

In this lesson, we explore running a macro or sub when clicking on a checkbox. NOTE: The check box is Boolean, so it's only TRUE or FALSE, checked or unchecked, respectively. This is super cool and very simple to play around with. Check it out!!

Fantastic Developer Tools:

[ExcelVbaIsFun]

Shana, to put contents from A1 to A2, try this:

range("A2") = range("A1")
or simply
[a2] = [a1]

To clear a cell or range of cells, like clearing A1, try this:

Range("A1").ClearContents

OR

Range("A1") = ""

the double quotes means blank.

[andand]

Very well explained and easy to follow. Thanks muchly!

[ExcelVbaIsFun]

Merry Christmas everyone. I hope God blesses you richly this upcoming year. Remember, you ARE a success!! Believe it, be it! Blessings my friends!! Dan

[ExcelVbaIsFun]

1. totally, named ranges would be awesome.
2. that should work, but here's some things to consider
a. make sure there's a row named exactly "test" no quotes
b. make sure you have an Activex checkbox that's named "Checkbox1"
c. make sure you have the click even within the sheet the checkbox is in, NOT in a module. To see where the code lives for the actual cbox, go to Design Mode and double click on that checkbox. Anywho, it should work under these conditions. Take off design mode, should work

[ExcelVbaIsFun]

Wonderful!! Good job Modelbaan. Thanks so much for sharing! Dan

[pcpmundo]

Nice demo.  I am currently working with forms via VBA and wanted to know if there is a way to have the option button open a secondary form if the answer is yes?  I will look at your other demos to see if I find what I need, thanks.

[MrVilst]

Great tutorial!
thanks for sharing
About if is there many checkboxes ( like one for each item sold ), do I need to do the same process for each checkbox?? ( hope no )

[shanalaurenc]

many thanks, please would you advise on how I could use named ranges to hide rows when I click a checkbox, So when I insert new rows it will automatically include the new rows or in the case if i delete some rows.

I have written 20 check boxes with VBA if i insert a new row all the code has to be adjusted, it is too time consuming to keep changing the row values in the vba code, thanks Dan

[shanalaurenc]

Hi Dan, would you know what the VBA code is to move cell contents, to another cell if click a checkbox next to the original content, I would like to clear out the contents as well from original cells, thank for your time.

[FK]

Thanks for the tutorial. Is there a way to save a coded ActiveX control and insert it to other worksheets and workbooks even?
I have coded an ActiveX check box in one of my worksheets. I want to insert the checkbox into other sheets to carry out the same function.

[ExcelVbaIsFun]

So it's checked by default? Go into design mode from the developer ribbon/tab and when in design mode right click on the checkbox>Properties. Change the property called "Value" from True to False. Makes the default unchecked, aka False instead of checked or True. Is that what you're asking? Dan

[ExcelVbaIsFun]

Thanks OptionGal, i'm loving your comments. . . Dan

[daryldr]

What if I what to relocate this code in to multiple cells with seprate cells?Will I need to write the code each time when I copy and past to another cell the link remains static. I have over 100 cells to duplicate this formula to!

[shanalaurenc]

Hi Dan, my active x check boxes have a tick sign in them by default any advise on how i can have them clear and so when i click them a sign should appear, and then disappear afterwards like normal check boxes?

[shanalaurenc]

Thanks Dan, I had it in a module, and my check box was not an active x check box, I should click on the sheet name and then > worksheet - This is where I paste some code in, be back with update

[vicentalonon]

this video gives me idea on the other way of using excels

[ultraflight]

How about if you want the value of the check box to be put in another spreadsheet (e.g. Sheet 2)? What will be the code? That would not be just range ("b2") right? Thanks in advance

[ji]

Can I password protect a check box? I would like several users to review an excel spreadsheet and check the box. So I would need several check boxes with different passwords for each. Can this be done?

[muratsenturk]

What about changing checkbox font size?

[MrPouHan]

It would be nice if someone in their video would explain the difference between form and active X.