Assigning checkbox to the cell

[Guest]

I am writing a macro for a button. When I push the button, it should insert a
new row with 10 cells in a table and assign checkboxes to the last 5 cells of
the row.

In a macro how do I assign checkboxes to the cells of the new row ? Is it
possible ? Thanks

 

[Guest]

Which checkboxes are you using. Control Toolbox Toolbar or forms Toolbar.

You say 10 cells in a table - what does that mean. Do you mean 1 row 10
cells wide? 10 full rows. 10 rows the width of the table. How is the
table defined. Where in the table do you want the 10 rows. Where would the
checkboxes be located relative to the 10 cells. What cells would they be
linked to?

 

[Guest]

A table has 10 columns. 1 row is 10 cells wide. 5 checkboxes are in the last
5 cells of each row, one checkbox per cell.

Sorry for not making it clear.

 

[Tom Ogilvy]

Assuming you want the row inserted above the activeCell, extending to the
right.
..
Sub AddRow()
Dim rng As Range, cell As Range
Dim cbox As CheckBox, i As Long
Set rng = ActiveCell.Resize(1, 10)
rng.Interior.ColorIndex = 3
rng.Insert Shift:=xlDown
Set rng = rng.Offset(-1, 0)
For i = 6 To 10
Set cell = rng(1, i)
Set cbox = ActiveSheet.CheckBoxes.Add( _
Left:=cell.Left, _
Top:=cell.Top, _
Width:=cell.Width, _
Height:=cell.Height)
cbox.LinkedCell = cell.Address(1, 1, xlA1, True)
Next i
End Sub

 

[Guest]

Thanks!

The only problem is that the checkboxes show their property names
(CheckBox1, Checkbox2, etc) and when you click on them they show the value
(TRUE or FALSE) in the cell. How do I turn that off ?

Also, if the active cell is not in the first column, the code inserts the
row in whatever cell is active. how do I make it insert from the first column
of the row ?

Thanks!

 

[Tom Ogilvy]

Sub AddRow()
Dim rng As Range, cell As Range
Dim cbox As CheckBox, i As Long
Set rng = Cells(ActiveCell.row,1).Resize(1, 10)
rng.Interior.ColorIndex = 3
rng.Insert Shift:=xlDown
Set rng = rng.Offset(-1, 0)
For i = 6 To 10
Set cell = rng(1, i)
Set cbox = ActiveSheet.CheckBoxes.Add( _
Left:=cell.Left, _
Top:=cell.Top, _
Width:=cell.Width, _
Height:=cell.Height)
cbox.LinkedCell = cell.Address(1, 1, xlA1, True)
Next i
End Sub