Row detection in Excel 2

[Daedelus]

I have an Excel worksheet I have set up to track how much time is spent on various activities. To make the time entry process quick and easy, each activity is listed on a row and days are listed on columns. Each row is given its own command button. When the user finishes a task, he presses the button, and Excel calculates how much time has passed since the last entry and enters that amount in the column of the current day:

[u]               | 3/1 | 3/2 | 3/3 | ...
Task 1 [button]| 0.5 | 1.0 |     |    
Task 2 [button]|     | 2.5 |     |    
[/u]

etc.

So pressing the button by task 2 on day 3/3 will enter the time that has passed in the appropriate cell.

The problem I have is that the button needs to know which row it is in to determine which cell to place the time in. The only way I have found to do this is to create a separate subroutine for each button, which provides the row number to the main subroutine that actually calculates the time. This would be fine if I had a few fixed tasks, but I would like to be able to freely insert new rows and buttons anywhere in the task list without having to go in and create a new subroutine for the new button, and re-edit the other subroutines so they point to their new row number.

It would be nice if I could assign a single routine to all of the buttons, and detect somehow the row number of the particular button that was pressed. Then inserting new lines would be easy. Is there a way to detect the location of the selected button?

 

[SkipVought]

Hi,

Each control has a TopLeft & BottomRight property that refers to a cell object. The cell (range) object has a row property.

So

row = YourControl.TopLeftCell.Row

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Daedelus]

Thanks! That helps, but it doesn't get me all the way home yet. In order to use it, I need to know which control was responsible for the function call. And unfortunately, I have not been able to figure out how to do this either (other than assigning each button its own subroutine).

That may be elementary, but I am still feeling my way around here. Thanks again for your help.

 

[SkipVought]

Well aren't you running a procedure in the button click event?

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

try this

In each buttonclick event

Private CommandButton1_Click()
   AssignAtivityTime CommandButton1.TopLeftCell.Row
End Sub
Sub AssignActivityTime(lRow as Long)
   'your assign value code
End sub

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Daedelus]

Sorry to take so long (10 months) to get back to this. I was hit with a very heavy assignment before I could go any further, and am only now getting back to this issue.

I created these buttons using the "Control toolbar" in Excel. Once the button was created, right-clicking on it provides a "Assign Macro" option, which I used to run the procedure. I have not been able to find a place to define events for these buttons. They do not appear anywhere in the project window that I have been able to find, so I don't know how to define click events for them.

Can you tell me how to define click events for these buttons, or create other buttons on the worksheet for which I can define click events?

 

[AnotherHiggins]

How exactly did you create the boxes?

You should go to View > Toolbars and click Control Toolbox. Click on the "Command Button" button and draw a button on the screen wherever you want it (beside task 2, for example). Right click on the button and select View Code. Copy and paste Skip's code there.

Note: Before running the code by pushing the button, you will have to come out of design mode. Do that by deselcting the button on the Control Toolbox toolbar that has a blue trianble with a pencil graphic.

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[Daedelus]

Sorry. I was wrong again (I did this over a year ago). I apparently created the buttons using the "Forms" toolbar, not the "Controls Toolbox" toolbar. No wonder I was having such trouble.

Thanks, and a belated thanks to Skip as well.

 

[AnotherHiggins]

Happy to help!


[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886