Checkbox code

[chacha]

Maybe someone could help me with this
The user specifies a number of new rows to be entered (nMembers) and in column 9 of the new rows I want to enter a checkbox, and center it in the cell. is this possible? I have tried to write the code but cant get it to work!

[vba]Sub InsertMembers()
Dim nMembers As Integer
nMembers = CInt(Range("C3"))
nMemStart = Range("S1")

Dim startPoint As Range
Set startPoint = Sheets("Sheet1").Range("A" & nMemStart) ' A9 is where we insert rows at

For i = 1 To nMembers
startPoint.Offset(i, 0).EntireRow.Insert
startPoint.Offset(i, 0) = i
'in column 9 i want to insert a checkbox in every row
startPoint.Offset(i, 9) = OLEObjects.Add(ClassType:="Forms.CheckBox.1")
Next i
End Sub[/vba]

[pnewton]

Just a thought
Copy a formatted row from the worksheet (which contains your check box) to another worksheet (hidden).
Name last row (empty row below data)
Use code to copy row from hidden worksheet, goto last row, paste (before)

I have code somewhere but will have to hunt out (built into specialised mailing list / emailer spreadsheet)

[mikerickson]

Why make checkboxes? A Selection_Change event can make the whole cell act like a checkbox.

[VBA]Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Cells.Count = 1 And Target.Column = 9 Then
Target.Value = Not (CBool(Target.Value))
End If
End Sub
[/VBA]

[chacha]

thanks for the help, I avoided using the checkbox, now I just have a simpe Y/N, but I have a similar problem with a dropdown menu.
I have inserted one, with all the information filled in and now i want to copy and paste it into column 4 of all the new rows, using the code above but I can't get it to paste in the correct location on the sheet.
something like this:[vba]For i = 1 To nMembers
startPoint.Offset(i, 0).EntireRow.Insert
startPoint.Offset(i, 4).Select
Call DropList
Next i
...
End Sub

Sub DropList()
ActiveSheet.Shapes("Drop Down 1").Select
Selection.Copy
ActiveCell.Select
ActiveSheet.DropDowns.Add(351, 75, 28.5, 15.75).Select
'I dont understand how these work ^ and I persume they determine the location to paste at!
ActiveSheet.Paste
End Sub
[/vba]

[lucas]

Question: Why are you trying to do all of these things on the fly....Why not just design the worksheet and use the vba and forumla's from there?

Why do these things have to be created....copied, etc. at run time?

[chacha]

its my final year project for collage and I guess some of these things never make sense!
the end user of the spreadsheet will enter a number (Members)
I need to create that many new rows with 1 to Members in the first column, a drop down menu in the 4th column, were the user can pick, for example, car manufacturer toyota, in column 5 another drop down list with different types of cars made by toyota