Batch-modify checkboxes on Excel
 

Where are the checkboxes? I'm gonna assume that they're on a worksheet.

Are the checkboxes from the Forms toolbar or the control toolbox toolbar?

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim OLEObj As OLEObject
Dim CBX As CheckBox

Set wks = Worksheets("Sheet1")

'checkboxes from the Forms toolbar
For Each CBX In wks.CheckBoxes
With CBX
.LinkedCell = .TopLeftCell.Address(external:=True)
.TopLeftCell.NumberFormat = ";;;"
End With
Next CBX

'checkboxes from the control toolbox toolbar
For Each OLEObj In wks.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




lillyanka wrote:

Is there a way to modify several checkboxes at the same time on excel?

I have about 30 checkboxes, and I need to link each one of them to a cell.
Is there a way to do it without going to the properties and changing them one
by one?

Thanks a lot!

--

Dave Peterson

Batch-modify checkboxes on Excel
 

They're from the control toolbox toolbar.

Thanks for your help!!

"Dave Peterson" wrote:

Where are the checkboxes? I'm gonna assume that they're on a worksheet.

Are the checkboxes from the Forms toolbar or the control toolbox toolbar?

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim OLEObj As OLEObject
Dim CBX As CheckBox

Set wks = Worksheets("Sheet1")

'checkboxes from the Forms toolbar
For Each CBX In wks.CheckBoxes
With CBX
.LinkedCell = .TopLeftCell.Address(external:=True)
.TopLeftCell.NumberFormat = ";;;"
End With
Next CBX

'checkboxes from the control toolbox toolbar
For Each OLEObj In wks.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




lillyanka wrote:

Is there a way to modify several checkboxes at the same time on excel?

I have about 30 checkboxes, and I need to link each one of them to a cell.
Is there a way to do it without going to the properties and changing them one
by one?

Thanks a lot!

--

Dave Peterson