• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

need help on macro

N

narayanan_olam

New Member
hi - I am using the attached template for recording activity times.

currently i am able to use the template for recording times for one activity only at one time..

how should i modify the macro if i want to record times for multiple activities at the same time?

Thanks
 

Attachments

  • template.xlsm
    87.3 KB · Views: 1
narayanan_olam said:
hi - I am using the attached template for recording activity times.

currently i am able to use the template for recording times for one activity only at one time..

how should i modify the macro if i want to record times for multiple activities at the same time?

Thanks
Click to expand...

Hi Narayan,

One simple work around that I can suggest is, first type all the activities in activity name column and then select the start time column adjacent to this activity and click on Start Timer button that will populate the start time for all the selected cells by using the following lines of VBA Code :

Code: 
  Range(Cells(Selection.Rows(1).Row, Selection.Column), Cells((Selection.Rows(1).Row + Selection.Rows.Count) - 1, Selection.Column)).Value = Now

And then to update the End time and duration, again repeat the same activities, select the start time for all the activities that are yet to be populated with the end time and click on Stop Timer button, by writing the following lines of code:
Code: 
'The following line populates the end time in column 23 for selected cells
Range(Cells(Selection.Rows(1).Row, 23), Cells((Selection.Rows(1).Row + Selection.Rows.Count) - 1, 23)).Value = Now
'The following line populates the duration formula in column 24 for selected cells
Range(Cells(Selection.Rows(1).Row, 24), Cells((Selection.Rows(1).Row + Selection.Rows.Count) - 1, 24)).FormulaR1C1 = "=IF(RC[-1]<>"""",RC[-1]-RC[-3]-RC[-2],"""")"

The other workaround that we can use is using the checkbox. Where we introduce a column before the activity with checkbox enabled and the user can select the checkbox and then click on the Start timer / stop timer button to update the start / end time.

Hope the above suggestions helps!!
 
hi - thanks for teh resp
Ramesh said:
Hi Narayan,

One simple work around that I can suggest is, first type all the activities in activity name column and then select the start time column adjacent to this activity and click on Start Timer button that will populate the start time for all the selected cells by using the following lines of VBA Code :

Code: 
  Range(Cells(Selection.Rows(1).Row, Selection.Column), Cells((Selection.Rows(1).Row + Selection.Rows.Count) - 1, Selection.Column)).Value = Now

And then to update the End time and duration, again repeat the same activities, select the start time for all the activities that are yet to be populated with the end time and click on Stop Timer button, by writing the following lines of code:
Code: 
'The following line populates the end time in column 23 for selected cells
Range(Cells(Selection.Rows(1).Row, 23), Cells((Selection.Rows(1).Row + Selection.Rows.Count) - 1, 23)).Value = Now
'The following line populates the duration formula in column 24 for selected cells
Range(Cells(Selection.Rows(1).Row, 24), Cells((Selection.Rows(1).Row + Selection.Rows.Count) - 1, 24)).FormulaR1C1 = "=IF(RC[-1]<>"""",RC[-1]-RC[-3]-RC[-2],"""")"

The other workaround that we can use is using the checkbox. Where we introduce a column before the activity with checkbox enabled and the user can select the checkbox and then click on the Start timer / stop timer button to update the start / end time.

Hope the above suggestions helps!!
Click to expand...


Hi Ramesh-- Thanks for the response..

the checkbox option would be more suitable considering my scenario..

i think the first two options would not be suitable.. as each activity will have a different start and end time.. and its unpredictable..

how to enable the checkbox option in the macro?

could u plz help ?
 
narayanan_olam said:
hi - thanks for teh resp



Hi Ramesh-- Thanks for the response..

the checkbox option would be more suitable considering my scenario..

i think the first two options would not be suitable.. as each activity will have a different start and end time.. and its unpredictable..

how to enable the checkbox option in the macro?

could u plz help ?
Click to expand...

Hi Narayan,

Not sure, how the Resume / Pass is working over here.. I could create a checkbox column and add based on the linked cell concept of the checkbox, the start time and end time gets updated accordingly.

I am here by sending across the updated template file.

Hope this helps!!

Thanks,
Ramesh Kumar.P
 

Attachments

  • template.xlsm
    81.4 KB · Views: 0
Ramesh said:
Hi Narayan,

Not sure, how the Resume / Pass is working over here.. I could create a checkbox column and add based on the linked cell concept of the checkbox, the start time and end time gets updated accordingly.

I am here by sending across the updated template file.

Hope this helps!!

Thanks,
Ramesh Kumar.P
Click to expand...

Hi - This is the format right ??the resume /pause button is for calculating the break between the activity.. the start, pause& stop macro is as per my requirement..

the concept of the checkbox should be like -- if i click one check box, the buttons should be activated..

in case i want to start another activity, i will press pause and again press start for the new activity...

i should be able to go back to the old activity and press stoip..

start, pause/resume & stop timings should be recorded for all parallel activities..

hope i am clear in my requirement..

the format looks good..

thanks
 
Hi Narayan,

I could give my best try with regards to the implementation of the Start / Pause and Stop timer implementation to the best of my knowledge.

Please find the attached updated file.

Thanks,
Ramesh
 

Attachments

  • Templatev1.0.xlsm
    80.4 KB · Views: 4
Ramesh said:
Hi Narayan,

I could give my best try with regards to the implementation of the Start / Pause and Stop timer implementation to the best of my knowledge.

Please find the attached updated file.

Thanks,
Ramesh
Click to expand...


hi ramesh - checked the macro .. firstly thanks so much for ur help..

few observations:
1. the start , pause and stop buttons- the macro is already there in the existing template i sent.. could we use that itself and just add a column for checkboxes beside the start button?

2. in the current template u have designed , can we show the start, pause & stop buttons permanently ?

3. also the format of the pause/resume button should be mm:ss only.
 
You must log in or register to reply here.
Back
Top