Hi,
I need to create an Excel with an unknown number of rows (can change)
and assign a checkbox and a command to each one of the rows.
The code of the control must know which row it is relating to.
Any ideas?

Thanks

Imry Linden
ClickSoftware Melbourne Australia

Imry,

This ought to get you started...

"-------------------------------
Sub SprinkleCheckboxes()
Dim lngRow As Long
Dim lngTotal As Long

lngTotal = 20
For lngRow = 2 To 20 Step 2
With ActiveSheet.Shapes.AddFormControl(xlCheckBox, _
Cells(lngRow, 2).Left, Cells(lngRow, 2).Top, _
Cells(lngRow, 2).Width, Cells(lngRow, 2).Height)
.Name = "Box" & lngRow
.OnAction = ThisWorkbook.Name & "!StartSomething"
End With
Next 'lngRow
End Sub
'---
Sub StartSomething()
Dim str As String
str = Application.Caller
MsgBox "Row number is " & Val(Mid$(str, 4))
End Sub
'---------------------------------

Regards,
Jim Cone
San Francisco, USA
"imrylin" wrote in message
ups.com...
Hi,
I need to create an Excel with an unknown number of rows (can change)
and assign a checkbox and a command to each one of the rows.
The code of the control must know which row it is relating to.
Any ideas?
Thanks
Imry Linden
ClickSoftware Melbourne Australia

Hi imrylin,

Here's another approach if you don't want to fill a sheet with controls
-- why not simulate checkboxes by using cells adjacent to the data?
Assuming that your data is in a sheet named "Data", and that the actual
data starts in B2, you could use column A as your checkboxes.

The following code would go in ThisWorkbook. It checks that you've
selected a cell in column 1 and within the data rows, then it colors
the cell and runs your code in another module.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)

Dim lastrow As Long
' quit if not on correct sheet or selecting more than a single cell
If Sh.Name < "Data" Or Target.Cells.Count 1 Then Exit Sub

' get the last row containing data
lastrow = Sh.Cells(Sh.Rows.Count, 2).End(xlUp).Row
With Target
If .Column = 1 And .Row 1 And .Row <= lastrow Then
If .Interior.ColorIndex < 1 Then
.Interior.ColorIndex = 1
DoNextThing ' run your macro "DoNextThing"
End If
End If
End With
End Sub

You can see that this only runs once for a given cell. Remove the "If
..ColorIndex..." line and it will always fire for one of the valid
cells.

Hope this also gives you some ideas,

Gary

Thanks Gary, I think that I will use your idea.
Imry

I should add that since you need to know the row, the line:

DoNextThing ' run your macro "DoNextThing"

should pass the row:

DoNextThing .Row ' run your macro "DoNextThing" on the row

and your "DoNextThing" sub (or whatever its called) should take the row
number as an argument:

Sub DoNextThing(rownum as Long)

Also, I suggested just filling the cell with a color, but you could use
a character, or text, or some fancy formatting if you want to make the
cells look and behave more like some sort of checkbox.

Gary