[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

Add a button to each row of a spreadsheet

nacooke

12/20/2006 10:27:00 AM

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
2 Answers

Jim Cone

12/20/2006 1:02:00 PM

0

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/primiti...


<nacooke@gmail.com>
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

Bob Flanagan

12/20/2006 1:28:00 PM

0

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.a...
Productivity add-ins and downloadable books on VB macros for Excel

<nacooke@gmail.com> wrote in message
news:1166610432.693378.21200@f1g2000cwa.googlegroups.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
>