checkbox/option button in specific cell in Excel - how to add to cell

agwalsh
agwalsh used Ask the Experts™
on
hi
I'm wondering how to do the following..
I want to set up a sheet so that the following happens...
The user ticks a checkbox/option button. This automatically gives the cell it is in a value e.g. -1 etc. I plan to have a calculations sheet set up with the formulas...but my first step is how do I add a checkbox that is linked to a specific cell so that I can reference that cell in a formula...
Thank you
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
This is actually quite easy to do.  However you may need to turn on your developer ribbon.  It isn't on by default.  

You can do this by:

open excel.
In the grey square between the binoculars and the edge of Excel right click.  
Select customize ribbon.  Click in the right hand column on the developer ribbon.  click ok.

From here it is pretty easy,  Just "insert" a form control.  You can assign it to a specific cell that you can use in formulas,

Note that you get different options when clicking on the checkmark you insert if you are in or out of design mode.  

I think the default is true false for a checkmark.
After you insert it you format control, and click on the control tab.  And then link it to a cell.

Please note that there are "form" controls and Active X controls.  The Active X are designed to be used with VBA, so if you are not familiar with VBA or just don't want to use it make sure you insert the FORM controls :)

-SA

Author

Commented:
@Script Addict - thank you - got all that. Now when I check this value I get true - how could I set this to become a value if checked e.g. when it's checked, the value of that cell becomes -1 for example...
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Well, you have two options.  You could either use a formula that uses an if statement

Don't put the true/false cell in the cell you need the value in.  Then just use a formula like

if(A1=True, -1, 0)  - where A1 is the cell you do have the checkmark linked too.

Or you can use the VBA switch and use an event trigger to edit the cell value based on whatever vba you want to code.


Personally I'd just have my formula assign a value based on the True/false, and an if statement in my formula.

Author

Commented:
Ok, that's fine. I can do that...I just thought there might be a way in the properties to do it that I hadn't seen...thank you!
A
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
Here is some code that you can use to create checkboxes automatically by double-clicking on a cell.

As written, it takes the caption of the checkbox from the original contents of the cell. It then creates a Forms toolbar checkbox in the cell being double-clicked and links the status of that checkbox to the same cell (TRUE/FALSE), only in a white font color so you don't need to look at it. You can interrogate the status of the checkbox by seeing whether the linked cell is TRUE or FALSE.

Install the macro in the code pane for a worksheet. Create your checkboxes. Then delete the code (if you wish) when you are done.

'Goes in code pane for worksheet containing checkboxes
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Adds a Forms toolbar checkbox to the cell being double-clicked
'Takes its caption from the value of the cell before it was double-clicked
'Checkbox value is linked to that same cell, using a white font color
With ActiveSheet.Checkboxes.Add(Target.Left, Target.Top - 1, Target.Width, Target.Height - 1)
        .Caption = Target.Value
        .LinkedCell = Target.Address
        .Name = "cb" & Target.Address(False, False)     'Name it like "cbA11"
End With
Target.Font.ColorIndex = 2     'White font color for linked cell TRUE/FALSE value
Cancel = True
End Sub

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial