Checkbox1.LinkedCel = ActiveCell -- "Why wont it work?"

[Time Traveller]

Have little bit of code that dumps Activex Checkbox on page, sizes it to fit
in nicely to the ActiveCell. Works beautifully, except I want to make the
linked cel the active cel as well, and I really don't want to have to go
into design mode and do it manually for every checkbox I create. For some
reason this code does not work as I expect. I'm sure theres a fundamental
principle I am unaware of.

Checkbox1.LinkedCel = ActiveCell

Also another question: When dumping the checkboxes on the page, they are
incremented by 1..eg Checkbox1, Checkbox2 etc. Seems to me there must be an
index somewhere that keeps track of these. How can I refer these checkboxes
by their ordinal number programmatically at runtime so that I can do
operations with the checkbox at that time, using the index or the ordinal
part of the string. I could write something to strip the ordinal part out of
the name, but that seems like a major kluge.

 

[Dave Peterson]

This worked ok for me:

Worksheets("sheet1").CheckBox1.LinkedCell = ActiveCell.Address(external:=True)

From a previous post that may help you:


If I knew how many checkboxes there were and they were named nicely, I'd do
something like:

Option Explicit
Sub testme()
Dim iCtr As Long
For iCtr = 1 To 4
Sheet1.OLEObjects("checkbox" & iCtr).Object.Value = False
Next iCtr
End Sub

If I wanted to get them all, but didn't know how many, I could get them this
way:

Sub testme2()

Dim OLEobj As OLEObject

For Each OLEobj In Sheet1.OLEObjects
If TypeOf OLEobj.Object Is MSForms.CheckBox Then
OLEobj.Object.Value = False
End If
Next OLEobj

End Sub

If you knew the names of just the checkboxes you wanted to change:

Sub testme3()
Dim iCtr As Long
Dim myCBXNames As Variant
myCBXNames = Array("checkbox1", "checkbox2")
For iCtr = LBound(myCBXNames) To UBound(myCBXNames)
Sheet1.OLEObjects(myCBXNames(iCtr)).Object.Value = False
Next iCtr
End Sub