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?

---
Message posted from http://www.ExcelForum.com/

[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.

--

Dave Peterson
ec3...@msn.com