Links to multiple Checkboxes

  • Thread starter Thread starter Bahboosh
  • Start date Start date
B

Bahboosh

I'm trying to link a range of cells that include checkboxes to an
adjacent column, but i dont want to go into each cell and link them
seperatley. Is there an easier way to do this?
Ex.:

A1:A100 include the checkboxes

C1:C100 linked cells

Thanks
 
There are two types of checkboxes--from the forms toolbar and from the control
toolbox toolbar.

Option Explicit
Sub testme01()
Dim CBX As CheckBox
Dim OLEObj As OLEObject

For Each CBX In ActiveSheet.CheckBoxes
CBX.LinkedCell = CBX.TopLeftCell.Offset(0, -1).Address(external:=True)
Next CBX

For Each OLEObj In ActiveSheet.OLEObjects
If TypeOf OLEObj.Object Is MSForms.CheckBox Then
OLEObj.LinkedCell _
= OLEObj.TopLeftCell.Offset(0, -1).Address(external:=True)
End If
Next OLEObj
End Sub

The .topleftcell.offset(0,-1) means to use the column to the left of the
checkbox.

The CBX code does the Forms toolbar checkboxes. The OLEObj code does the
Control toolbox toolbar checkboxes.
 
Ps. I'd use

..topleftcell.address(external:=true)

And format those cells (whole column???) with a custom format of:

;;;
(3 semicolons)

The cells look empty except when you look at the formula bar.
 
Back
Top