Task Recorder Version1

I’ve made my first iteration of a program to record my tasks (genesis here).

J-walk suggested:

Create a bunch of general work-oriented categories. Then, when the timer goes off, have XL display a Userform that lets you allocate the last hour by percent in each category. Even if you’re away from your desk when the timer goes off, the code can determine the time since the last entry and adjust.

Good idea, but I have two problems with it: First, it locks me into categories – or at least I can’t change them on the fly. That’s probably not as big of a deal as I think. In fact it might be better than having me go from too specific to too general in the category department. So maybe I don’t have a problem with it.

Second, I want to record the actual tasks. I want to answer the question, “I spent 4.2 hours on IT? What the hell did I do?”

Instead of recording tasks every half hour, I’ll record them whenever I switch tasks. Sometimes that will be five minutes and sometimes two hours. Here’s basically how it’s going to work:

When the workbook is loaded it checks to see if there are any entries. If there aren’t any entries or the last entry if not from today, a new entry is created that sets the time the day is started. Then the hotkey Ctrl+Shift+T is assigned to open the form. Finally OnTime is used to open the form in 30 minutes.

If I open the form and create an entry, the timer restarts. The timer serves as a reminder in case I forgot I’m supposed to be recording stuff. For longer tasks, I’ll just dismiss the form if I don’t have an entry to make.

The three buttons next to the date picker decrement the dtp by 10 minutes, increment the dtp by 10 minutes, and set the dtp equal to the current time, respectively. If I come back to my office and the form is showing, I can quickly change the time to now and record the task.

The Category combobox is seeded. That is, I came up with a number of categories that I wanted to include right from the start. But I can still make up whatever category I want. The combobox is populated by the seed categories plus whatever other categories exist in the database. I suppose this will get slower as the database gets bigger.

The listbox on the bottom shows the last eight entries, just for some perspective.

When saved, the time, category, and task are recorded onto the workbook. Right now the workbook is simply hidden. I’ll either copy it into XLSTART or make it an add-in.

You can download TaskIt.xls.zip

Update: You can download TaskItv2.xls.zip

Update: You can download TaskItv3.xls.zip

Posted in Uncategorized

29 thoughts on “Task Recorder Version1

  1. I don’t think you can save data into an add-in, at least not the last time I tried. You may need a regular workbook (or maybe a Jet database) as a back end.

  2. nice app, one error, though (at least it did not work in the sheet I downloaded):
    On-the-fly categories did not make it into the dropdown list. I changed a line of code and then it worked. In Public Sub RecordTask() replace the line

    colCats.Add vaCats(i), CStr(vaCats(i))


    colCats.Add vaCats(i, 1), CStr(vaCats(i, 1))

    Of course, now it will also pick up “Start” as a category, but I can live with that.


  3. Rob: I’m sure you can. I use add-in worksheets as storage all the time. You just can’t see them, so you have to remember where you store stuff.

    teylyn: Good catch, thanks.

  4. I’ve had issues saving data to an addin if I opened two instances of Excel, both running the addin. I can’t remember exactly what happened, but something like saving the changes (to the addin in the 2nd instance) to the same directory as the active workbook.

  5. You certainly can store data in an add-in. It’s a workbook, containing worksheets, just invisible. However, I don’t think it’s such a good idea to do so. There are the issues with having multiple Excel instances using the same add-in, and fighting over whose changes to save. Also, it’s best to keep data and algorithms separate, so you can replace one without hosing the other. I also like to keep track of add-ins by last saved date in Windows Explorer, which you can’t do if you keep changing the contents of the add-in.

  6. This is a great tool. Please could someone tell me where the tasks and times are logged to as I am a excel novice?

    Thanks for your help!!!

  7. Jon: I don’t store real data in add-ins, but I do store user options. Something that a normal person might write to the Registry, I just store on a sheet in the add-in. Do you think that’s a problem?

  8. Dick –

    Like I said, I don’t like modifying the add-in. If I send an update, those user settings are gone.

    I use good old fashioned VB I/O to save settings in a text file. Kind of like an INI file without all the baggage. I leave the registry alone, except for my installer routines.

    I save all kinds of stuff, if I remember: position of a dialog last time the user dismissed it, position of a custom toolbar the last time Excel closed, the last options the user selected in a dialog, the last directory they accessed in GetOpenFileName or GetSaveAsFileName, and whatever else I think that the user would appreciate.

  9. Hello Dick,

    When I open each version I get the following message “Microsoft Forms – Could not load an object because it is not available on this machine”. Then the VBA widow opens and the following is highlighted:
    ‘ Get the application directory.
    sPath = ThisWorkbook.Path
    If Right$(sPath, 1) “” Then sPath = sPath & “”
    The specific text highlighted is: Right$

    Dave T

  10. Hello All,

    Any suggestions would be appreciated…
    After receiving the message “Could not load an object because it is not available on this machine”
    I searched the internet for a solution to this problem and within the VBA editor went to ‘Tools’ > ‘References’ and found the following “MISSING: Microsoft Windows Common Controls-2.6.0?.
    Can anyone please offer me suggestions as to how to fix this error message.

    Dave T

  11. Dick,

    I’ve been trying this out since you posted it last month, the last two weeks full time. It’s very handy when filling out time sheets and will provide me with backup data in case my time is challenged. When will we see the commercial version? I have a few suggestions before release.


  12. Ed: I’m still using it too. I hardly notice that I’m tracking time anymore, so it’s pretty effortless. Suggest away. Either here or to dkusleika@gmail.com. If I gave you a date for a polished version it would just be a lie. But it is on my list.

  13. Dick,
    how is it possible, to edit the recorded data items afterwards? (cannot access the data in personal.xls or another hidden worksheet).

  14. Dick, yes I’ve already read this – but in my case ‘unhide’ reveals the personal.xls macro sheet, and this one remains always empty?!

  15. ok, thanks. But in my case, only shows the first entry: “personal.xls” Nothing else.Everything else seems to work.

  16. Dick,
    I’ve been using since we first talked. It is the simplest and nonintrusive way to track my time. I tinkered with it til I broke it once or twice, now it does exactly what I need.

    Sum of DurationDate
    Category21-Dec22-Dec23-DecGrand Total
    Grand Total8:42:259:46:296:41:0925:10:03

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.