Hi

I'm working on a spreadsheet that is dynamically populated from a
database query. I don't know how many rows will be returned by that
query.

What I would like to do is have a button at the beginning of each row
that will execute a function, passing in some of the values from the
the row.

Is this possible at all? I looked into adding a button using
ActiveSheet.OLEObjects.Add but this seems to have a maximum of 600
controls which is fewer than I may need. The excel form button is
quicker to use, but not sure if I can use it to execute my own code
with parameters rather than just a macro.

It doesn't have to be a button - just a way of executing code based on
the row the user is concerned with (e.g. could be double clicking on a
cell etc) I

Any tips or advice would be greatly appreciated.

Many thanks

Nick

Nick,
Your double-click idea will work.
It is not a good idea to put several hundred controls on a sheet.
Look in the sheet module. It has a double-click event that can
be used. Try code similar to the following...

Option Explicit
'Jim Cone - San Francisco, USA - December 2006
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim lngRow As Long
'Assumes user will double-click in column A.
If Not Application.Intersect(Target, Me.Columns(1)) Is Nothing Then
lngRow = Target.Row
'Row must have at least two cells with an entry and must be a row
'occurring below the header area.
If Application.CountA(Me.Rows(lngRow)) 1 And lngRow 3 Then
Call OtherSub(Target(1, 3).Value, Target(1, 4).Value, Target(1, 6).Value)
Cancel = True
End If
End If
End Sub
----------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



wrote in message
Hi
I'm working on a spreadsheet that is dynamically populated from a
database query. I don't know how many rows will be returned by that
query.
What I would like to do is have a button at the beginning of each row
that will execute a function, passing in some of the values from the
the row.
Is this possible at all? I looked into adding a button using
ActiveSheet.OLEObjects.Add but this seems to have a maximum of 600
controls which is fewer than I may need. The excel form button is
quicker to use, but not sure if I can use it to execute my own code
with parameters rather than just a macro.
It doesn't have to be a button - just a way of executing code based on
the row the user is concerned with (e.g. could be double clicking on a
cell etc) I
Any tips or advice would be greatly appreciated.
Many thanks
Nick

Add a menu item to the Tools menu that runs the code. The code can
determine the active cell. Or, it can display a confirmation box to confirm
the row or allow a row to be selected and then run.

Bob Flanagan
Macro Systems
144 Dewberry Drive
Hockessin, Delaware, U.S. 19707

Phone: 302-234-9857, cell 302-584-1771
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

wrote in message
ps.com...
Hi

I'm working on a spreadsheet that is dynamically populated from a
database query. I don't know how many rows will be returned by that
query.

What I would like to do is have a button at the beginning of each row
that will execute a function, passing in some of the values from the
the row.

Is this possible at all? I looked into adding a button using
ActiveSheet.OLEObjects.Add but this seems to have a maximum of 600
controls which is fewer than I may need. The excel form button is
quicker to use, but not sure if I can use it to execute my own code
with parameters rather than just a macro.

It doesn't have to be a button - just a way of executing code based on
the row the user is concerned with (e.g. could be double clicking on a
cell etc) I

Any tips or advice would be greatly appreciated.

Many thanks

Nick