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?

 

[ianpwilliams]

Ok that must be the problem. It seems like my check boxes come from the Control Toolbox. I would attach the spreadsheet, must I'm not allowed attachments apparently.

 

[Snakehips]

Then try this....

For Each shp In Sheets("FCC CHECKLIST").Shapes
If shp.Type = 12 Then shp.OLEFormat.Object.Object = False
Next shp
End Sub

 

[ianpwilliams]

Great, that works a treat, thanks!

One other question while I'm here if you can help.

I've created a macro to remove any empty lines from a particular sheet:

' Remove empty lines
'
If Range("B7") = "" Then
Rows("7").EntireRow.Hidden = True
Else
Rows("7").EntireRow.Hidden = False
End If
Range("B1").Select
End Sub

The thing is, I have many, many rows with formulas on (which individually either produce text or don't), adn I would like to create some kind of loop where the above can be applied to a range of rows, namely rows 7-42, 46-68, and 72-96. Is there a way I can modify this macro to do that?

 

[ianpwilliams]

Never mind, I found a macro elsewhere to clear a selection of rows, which I was able to modify and use. Thanks again!

 

[MelindaRose]

I currently have an attendance sheet with check boxes next to the names of attendees. I have the check boxes linked to another sheet in Excel that has names in the A column and dates in B-P. When a name is checked it shows TRUE and if it's unchecked it shows FALSE. Since I'm tracking the attendance from multiple meetings on this one sheet, I'd like it to be a little more clear by simply looking at the report. Is it possible to have nothing show up if the box isn't checked and to have something like a line or an X appear if the box is checked?

I'm using Excel 2010 and I'm very much a novice so please let me know if I can clarify.

Thank you

 

[Snakehips]

MelindaRose. It's not entirely clear to me how your data is arranged and therefore how your check boxes are linked.
Is it a single attendance sheet with 15 tick box per name, one for each of 15 meetings?

Here is one thought that may or may not be appropriate and will avoid any need to use vba.
Columns B - P have meeting date as header and a TRUE or FALSE linked to checkboxes ?
Duplicate the date headers in Q1 :AE1
Enter formula in Q2 ..... =IF(B2=TRUE,"X","")
Drag that formula down as far as required and across to column AE
Then hide columns B:P
You will see X for attendance otherwise blank.

Hope that helps. If not then please clarify the data layout.

 

[MelindaRose]

Thank you, I was able to figure something out with conditional formatting. I appreciate the suggestion and am sorry for the lack of clarity earlier! I do have another question though: is it possible to shift an entire column of checked boxes linked cells one column over? In this instance I have check boxes from I2-90. Right now they're linked to corresponding rows in the C column. I'd like to keep the rows the same and just shift them to the D column. Can I do this without linking each individually?

 

[Snakehips]

If your data layout will allow you then you may be able to manipulate the relationship by inserting and deleting columns. As you insert and delete / delete columns, Excel will adjust the linked column relationship accordingly.

Failing that, try running the below code which assumes you have ActiveX checkboxes rather than Form type?? and that the 89 in column I are the only ones in the sheet.

Sub Change_Check()

Dim sh As Shape
For Each sh In ActiveSheet.Shapes ' Assumes are only checkboxes in I2:I90
Select Case sh.Type
Case 12 ' is activex checkbox
sh.OLEFormat.Object.LinkedCell = Replace(sh.OLEFormat.Object.LinkedCell, "C", "D") 'change C to D column of cells to be linked
Case Else
End Select
Next sh
Range("C2:C90").ClearContents
End Sub

Shout up if you need code for Form type.