delete caption on multiple check boxes

  • Thread starter Thread starter confused??
  • Start date Start date
C

confused??

I would like to add multiple check boxes (up to 1000) and link each check box
to it's corresponding cell as well as deleting the caption beside each check
box and have the check boxes centred in the cells.

Can anyone help me?

This is what i have so far but i can't work out how to delete the captions
and centre the checkboxes:

Sub addCBX()
Dim myCBX As CheckBox
Dim myCell As Range

With ActiveSheet
.CheckBoxes.Delete 'nice for testing
For Each myCell In ActiveSheet.Range("c2:c1000").Cells
With myCell
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)
With myCBX
.LinkedCell = myCell.Address(external:=True)
'..Caption = "" 'or whatever you want
'.Name = "CBX_" & myCell.Address(0, 0)
End With
.NumberFormat = ";;;"
End With
Next myCell
End With
End Sub
 
Try the below

Sub addCBX()
Dim myCBX As CheckBox
Dim myCell As Range

With ActiveSheet
.CheckBoxes.Delete 'nice for testing
For Each myCell In ActiveSheet.Range("c2:c1000").Cells
With myCell
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)

With myCBX
.LinkedCell = myCell.Address(external:=True)
.Caption = ""
.Width = .Height
.Left = .Left + (myCell.Width / 2) - (.Width / 2)
End With
.NumberFormat = ";;;"
End With
Next myCell
End With
End Sub

If this post helps click Yes
 
Back
Top