Modifying checkbox "TRUE" and "FALSE" labels

[ianpwilliams]

I have a column of checkboxes on an Excel spreadsheet. I've linked each checkbox to the cell where it is placed, and I have resized the checkbox boundary to roughly the size of the cell. Now I have two options:

1 - I can set the properties of the checkboxes to print as "Yes", which will print all of the checkboxes, whether they are ticked or not.

Or:

2 - I can set the printing properties to "No", which then provides a printout with no checkboxes at all, but with the values of "TRUE" and "FALSE" in each cell.

I'm not too keen on option 1, because it's hard to see which have been ticked and which haven't. But I don't like the "TRUE" and "FALSE" of option 2. What I would really like is to have a tick (no doubt using the Marlett" font?) in each cell where a checkbox was ticked, and nothing if the checkbox wasn't.

Is that possible?

 

[Snakehips]

ianpwilliams,

Welcome to MrExcel.

Perhaps try this as a workaround.

You will need to have a free column or cells that you can use as a 'helper' column to link to your checkboxes.
It can be a column that you choose to keep visible but have outside your data/print range or it could even be within the print range but hidden or with text colour set to background colour so that the text is 'invisible'.

So, eg, Checkbox sized to cover say cell B3. Your helper column = column C.

Set print property for checkbox to NO.
Set linked cell to C3
Format Cell B3 as you will for a tick eg Wingding2 (Uppercase P = a tick)

Then put the formula =IF(C3,"P","") in B3

Hope that helps.

 

[ianpwilliams]

Excellent, that worked perfectly! Thanks very much!

 

[ianpwilliams]

I have a follow-up question. There is a macro in the spreadsheet (in another tab) which clears various data from the other tabs. I would like to insert a command in this macro that will also clear any selected checkboxes from this tab. Please can you tell me what command I would need to do this?

 

[Snakehips]

I have a follow-up question. There is a macro in the spreadsheet (in another tab) which clears various data from the other tabs. I would like to insert a command in this macro that will also clear any selected checkboxes from this tab. Please can you tell me what command I would need to do this?

When you say clear, do you mean un-tick? Is 'this tab' the tab from which the macro is being run? Please post the macro or the significant part of it.

 

[ianpwilliams]

Unfortunately I can't post the macro, as it's not my spreadsheet. But what happens is, the macro runs from a different sheet within the spreadsheet, and it clears various selections of cells from various other sheets (basically reverting the spreadsheet to it's original untouched version). I would like to insert a command or commands into the macro to also select the sheet where the check boxes are (I know how to select a certain sheet within a macro), and untick all the ticked check boxes (or just untick the ticked ones, either way is fine).

I created the check boxes the way you described above, so I have 10 checkboxes (in Cell A8&9, A10&11 etc), with 10 linked cells (A36, A37 etc), which are all Wingdings 2, and which all say "FALSE" when the corresponding check box is unticked, and "TRUE" when the corresponding check box is ticked, and the cell beneath each check box has the formula "=IF(A36,"P","")", "=IF(A37,"P","") etc.

I tried adding a command to the macro to make all of A36 to A45 equal "FALSE", in the hope that it might make all the check boxes unticked, but it didn't work.

 

[Snakehips]

Ian,

Try adding the following lines to the macro code....

For Each shp In Sheets("YourSheet").Shapes
If shp.Type = 8 Then shp.OLEFormat.Object.Value = False
Next shp

Hope that helps.

 

[ianpwilliams]

I tried that but unfortunately it didn't work. This is the code I tried:

'
' New code - Reset FCC check boxes
'
For Each shp In Sheets("FCC CHECKLIST").Shapes
If shp.Type = 8 Then shp.OLEFormat.Object.Value = False
Next shp

And it said:

"Object doesn't support this property or method", with this bit highlighted:

'
' New code - Reset FCC check boxes
'
For Each shp In Sheets("FCC CHECKLIST").Shapes
If shp.Type = 8 Then shp.OLEFormat.Object.Value = False
Next shp
End Sub

 

[Snakehips]

Ian,

I'm not sure what to suggest.

The code works for me in both Excel 2003 and 2007.

I have assumed from your initial post that you are using FORMs check boxes????

 

[ianpwilliams]

I've been using the Check Box from the Control Toolbox.

How about if I upload a trimmed version of it?