delete caption on multiple check boxes

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
 
J

Jacob Skaria

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
 
C

confused??

Thanks very much for the quick reply! That worked beautifully, thanks again!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

format checkbox 2
Checkbox Offset 2
Check Box Value 1
Macro for checkbox generation 9
Excel Checkbox Alter Font by VBA 3
Dynamically assign macro 23
Combo Box Code 3
Multiple Checkboxes 3

Top