Creating a Control Array

[imrylin]

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

 

[Jim Cone]

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

 

[GaryDK]

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

 

[imrylin]

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

 

[GaryDK]

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