Tick boxes

[andrewdelmont]

How di I add tick boxes to forms? At the moment Im using a drop down box with the option to choose an "X", but want to use something that can be checked or unchecked just with one click.
I'm not well versed in programming so a non-programming answer if possible please.
Thank-you

 

[Smitty]

Since you're not well versed in programming, I assume you're talking about worksheet forms and not VBA user forms?

You can select the Check Box control from the Forms toolbar. Once drawn you can goto Format control to add a cell link and other options.

Hope that helps,

 

[pbt]

For Excel 2007 > Click on the Developer Tab
Click on Insert
Then click on the Check Box control > then click on your sheet
From there you can do as Smitty suggested.

Harry

 

[andrewdelmont]

Great thanks. Didnt have the Developers Tab activated which is why I souldnt find it. Got it now though, thanks

 

[andrewdelmont]

Next thing is, is there a way to position the check boxes in the cells automatically so they all line up correctly?

Also, is it possible to make some of them disappear based on a cell value? I have a number of forms which are used for one or two people. When its one person, I select "Single Life" on the input sheet. Previously I used conditional formatting to white out the cells I didnt need. That obviosuly wont work with the chek boxes though.

Thanks

 

[Smitty]

Next thing is, is there a way to position the check boxes in the cells automatically so they all line up correctly?

You can do that manually and align them with Drawing tools, and check their properties to not size or move with cells. Or you could use the TopLeftCell property in VBA.

For the second you can use the Sheet_Activate event to test the Single Life value and hide the check boxes accordingly. Here's an example:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Activate()<br>    <SPAN style="color:#00007F">If</SPAN> Sheets("Input Sheet").Range("A1").Value = "Single Life" <SPAN style="color:#00007F">Then</SPAN><br>        ActiveSheet.Shapes("Check Box 1").Visible = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>