how do i create a new check box in excel?

[Guest]

I'm trying to create a new check box in excel. I'm able to place the box in
a cell, but not sure how to use the formula function. DO I have to create a
macro? The basic formula I'd like to use is if the box is checked, I want
another cell to read, "verified." In addition, I want the entire sheet
protected, other than the check box option. Can someone help? THis is my
first time trying this, so a step by step process would really help!!!
Thanks,
Marisol

 

[Biff]

DO I have to create a macro?

No. Use a checkbox from the Forms toolbar.

When you click on the checkbox it is either checked or unchecked. When it is
checked it evaluates to TRUE and when it's unchecked it evaluates to FALSE.
You can base your formula on these values. You have to link the checkbox to
a cell and that linked cell will display the value of the checkbox, eithe
TRUE or FALSE. This linked cell can be hidden or disguised so that nobody
knows it's there.

For example, you can place the checkbox "in" cell A1 and also use cell A1 as
the linked cell. The checkbox is an OBJECT and objects don't actually reside
"in" cells, they "live" on top of the cell(s). So,

Right click any toolbar
Select Forms
Click the checkbox icon and navigate to the location you want to place it
then left click
Adjust the size and shape as desired
Now, right click the checkbox and a menu will appear
Select Format Control
Select the Control tab
Enter the cell link location, assume A1
Click the Protection tab, make sure Locked and Lock Text are checked
OK out
Now, right click on the checkbox again and select Edit Text and change the
caption if desired

Go ahead and try it out

Now, assuming A1 is the linked cell........
Select cell A1 and set the font color to be the same as the fill color

You're all set with the checkbox
Now, set up your formula. All you need to do is test whether the linked cell
equalls TRUE

The basic formula I'd like to use is if the box is checked, I want
another cell to read, "verified."

=IF(A1,"Verified","")

Now, set your sheet protection......

The linked cell has to be unprotected otherwise the checkbox won't work
properly.

Select the ENTIRE sheet
Goto Format>Cells>Protection tab
Select Locked
OK
Now select the linked cell, A1
Goto Format>Cells>Protection tab
Unselect Locked
OK

That's it. Now you can set your sheet protection.

Biff

 

[Guest]

It worked. Thanks for your help. If the statement is false, I changed it to
say, "pending." How do I change the font color whenever the statement comes
out false?

The worksheet I'm working on will be used by others in my workplace. Once
the employees make corrections, they enter the date next to the exception on
the spreadsheet I created. I don't want them to access anything but entering
the date. This includes protecting the check box. I tried doing the sheet
protection for specific users/ranges; however, I'm afraid that I will forget
to protect the sheet again once I unprotect it and everyone will have access
to it. That's my main concern. Any suggestions? The worksheet is saved on
the public network at work.

 

[Biff]

How do I change the font color whenever the statement comes

out false?

Use conditional formatting.

Select the formula cell. I'll assume that cell is B1.
Goto the menu Format>Conditional Formatting
Select Formula Is
Enter this formula in the little box on the right: (adjust the cell ref as
needed)

=B1="Pending"

Click the Format button
Select your desired style(s)
OK out

As far as something to remind you to protect the sheet......hmmm.....

I don't know! Maybe just put the word Protect in a prominent cell so that
you'll see it and it'll remind you.

Biff

 

[Guest]

Similar to this...

I have multiple checkboxes on my spreadsheet that are each 'on' their own
cells. I'm trying to figure out how to center each one relative to its
cells. The best I can do is manually move it around on the cell with a
mouse. With several of them in a line, my line is a bit crooked.

Thoughts?

 

[Biff]

Try this:

Select all the checkboxes by holding down the CTRL key and left clicking on
each of the checkboxes.

Open the DRAWING toolbar

In the left hand corner of the DRAWING toolbar you'll see DRAW and and
arrow. Click on that arrow. It will select all the checkboxes. Now, click
the DRAW button. Select Align or Distribute>Align Center. After you're done
click the arrow to deselect the checkboxes. Close the DRAWING toolbar.

Biff