I have exactly 512 checkboxes that I need to format with their own
individual cell link and it's getting monotonous!! :(
The ideal situation would be for Excel to allow me to copy and paste
the checkboxes and have it automatically change the values as it does
when you copy a formula (IE: When you duplicate "=A2+100" down a column
and get "=A3+100", "=A4+100" etc etc down the column)...but I know that
checkboxes don't work that way.
Anyway...any suggestions?
---
Message posted from http://www.ExcelForum.com/
I like to have my linked cell directly under the checkbox (carefully placed
within a cell!).
Then I format that cell with a custom format of ;;;. The cell looks empty from
the worksheet--but you can tell what's there in the formulabar.
Here's a routine that does what I like <bg> for both types of checkboxes:
Option Explicit
Sub testme()
Dim myCBX As CheckBox
Dim OLEObj As OLEObject
For Each myCBX In ActiveSheet.CheckBoxes
With myCBX
.LinkedCell = .TopLeftCell.Address(external:=True)
.TopLeftCell.NumberFormat = ";;;"
End With
Next myCBX
For Each OLEObj In ActiveSheet.OLEObjects
With OLEObj
If TypeOf .Object Is MSForms.CheckBox Then
.LinkedCell = .TopLeftCell.Address(external:=True)
.TopLeftCell.NumberFormat = ";;;"
End If
End With
Next OLEObj
End Sub
And you could always use:
.topleftcell.offset(0,-1).address(external:=true)
to use the cell to the left of the checkbox.
--
Dave Peterson
ec3...@msn.com