Cell Link Quick Access

[Josiah]

Anyone know a way to QUICKLY enter a cell link value rather than right
clicking to access "Format Control" (or clicking "CTRL - 1")?

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?

 

[Dave Peterson]

Where do you want the linked cells and what kind of checkboxes are they (from
the Forms toolbar or from the Control Toolbox Toolbar).

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.