Building Activity Log (Notes)

Step One: Gather some information

For the sake of this project, all of my "people" are an aggregation of people I've met and projects I've worked.  For a student/developer who is trying this project or any other, you will need to know:

1. Who will be entering information
2. What other sources of data will you use, e.g. reports/data-dumps from other systems.
   NOTE ON NEXT TWO (2) POINTS: In many cases, spreadsheets are an end-point product and typically do not get uploaded into other systems.  
3. Who will be using the information.  This will drive sorting 
4. What will be using this information.  (including other excel reports, Word documents,  will be using the output of this 
5. What the current activity log entails, especially what content is normally gathered
6. Other Excel worksheets they use for logging calls or visitors.

Step Two: Design and Create the Initial Worksheet

An activity log is usually very simple.  This one is no exception.  It starts with only three columns.  More can be added later.

Example 1: Starting Point for an Activity Log

Of course, the header row should be formatted, but I'll leave that for later

The tasks to do are

• Review forms used  to collect the information that goes on the Activity Log they currently use.  It may be a columns and rows log, a set of forms on a clipboard or screen, or just scratch paper.
• Enter the headers for the information they currently gather
• Organize in a way you think a person would (a) enter the information and (b) expect to read it. 
• Lock the headers (row 1).
• Format the Date/Time Stamp using te Excel Format Cells feature
• Change the name on the worksheet's tab to something meaningful to the client
• Save the Workbook with a name that is meaningful to the user.

Notes To Developers:

1. Build the worksheet with only the columns you are certain are needed.
2. Do not be surprised if the current log isn't in neat rows and columns.  Many activity logs are generated after the fact, at the end of a shift or the end the week using the 
3. Format headers like other reports you've seen used by the client
4. Bring to the review suggestions about other columns that might be added.
5. Keep expectations low as to the sophistication of what will be coming down the pike.  Avoid showing drop-downs, automated formatting, etc
6. Ask for suggestions on formatting the headers
7. Review with the user
8. Ask

Step Three: Review and Refine the Worksheet

Discuss the worksheet with the client, focusing on what is important to keep/add and what should be removed.

At this point the client can be advised as to the next level of the worksheet: automating the date-time stamp.

If the client would like, the worksheet can be used as-is.  The instructions are simple

• Type in the time the call came in
• Type in who called
• Type in what was said/decided during the call

Leave the client with the message "If there is anything we need to add or remove, let us know.  Sometimes these things can be added on the fly, sometimes they take some planning.  If they cannot be added immediately we can put it in a list for you (the client) to prioritize."

Follow up from the meeting

1. Document what was decided and what was requested
2. Send the client a copy to confirm you know what is going on and to give them one more chance to think about it
3. Per the feedback from the client
• Add/remove columns 
• Change column headers
• Modify the formatting
• Change the worksheet name

Step Four: Add a Date/Time Stamp

Notes To Developers: 

1. As a matter of practice, I put all Constants in a single Module, and Global Variables in another.  They are named "DD_Global_Constants" and "DD_Global_Variable".
   MODULE: DD_Global_Constants
   Public Const ActLog_dblTimeStampColNum As Double = 1
2. Often what could be a global constant is broken into a constant and a variable.
   MODULE: DD_Global_Constants
   Public Const DateFormat_yyyy_mm_dd_hh_mm_ss as String = "yyyy-mm-dd hh:mm:ss"
   MODULE: DD_Global_Variables
   Public ActLog_strTimeStampFormat as String

Date/Time Stamp Code

The following code is all it takes, along with the data declarations above

MODULE: ThisWorkbook (unless renamed)

Private Sub Workbook_Open

    ActLog_strTimeStampFormat = DateFormat_yyyy_mm_dd_hh_mm_ss

End Sub

MODULE: Worksheet, probably still called "Sheet1 (<name entered on the tab>)" unless it has been given a CodeName

Private Sub Worksheet_Change(ByVal Target As Range)
    With Cells(Target.Row, ActLog_dblTimeStampColNum )        If Len(.Value) = 0 Then            .Value = Now            .NumberFormat = ActLog_strTimeStampFormat
        Else            ' We'll Add something here later
        End If    End WithEnd Sub

Actions

1. Create modules for Global Constants and Global Variables and name them accordingly
2. Add the definitions for the Global Constants and Global Variables
3. Open the ThisWorkbook module, use the left drop-down to select "Workbook" and the header and end statement for the Workbook_Open event Sub will be created.  
4. Enter the code from above in the Workbook_Open event Sub
5. Open the worksheet module, use the left drop-down to select "Worksheet" and the header and end statement for the Worksheet_SelectionChange event Sub will be created.  Leave this as-is because it will be used later.
6. Use the right-hand drop-down to select the Worksheet_Change event.  The header and end statement for the Worksheet_Change event Sub will be created
7. Add the code from above
8. Test and refine

Step Three: Control the Input Fields

After this step is completed, the users should be permitted to add information only on the next line of the worksheet.  

• Use cell and worksheet locking to prevent new information from being added anywhere else.
• When the user adds the first information of a new call/visitor, time-stamp that row.
• When all cells of the row are completed, prompt to save/lock the row.
• Editing can be done by double-clicking on the cell.  

Step Four: Worksheet Validation 

Add a column for the reason for the call, e.g. Daily Check In, Vehicle Problem, Order Issue, Delivery Issue for a call log of delivery drivers.  For a visitor log, the reasons may be something like Vendor - Sales, Vendor - Delivery, Maintenance, Employee Interview, Contractor - Other.

• Use a drop-down for the reason column entries.  
• The reasons should be in a named range, allowing for user maintenance.
• The worksheet with the reasons should be Hidden and password protected.
• Password for  worksheet with the Reasons should shared with the manager.
• For the callers, assuming there is a list of drivers who might call

Step Four: Add who entered the data

Using the network ID of the person who enters information, populate the log with the name of the person taking the call (greeting the visitor).

Step Five: Add a form

Create a Userform for creating and editing the log entries.

Step Six: Annotate Changes

When a change is made, create a Comment for the cell, logging the time/date of the change and the original value.  

If subsequent changes are made, log each stage of change into the comment.

Update the Userform so it shows the history of changes.

Step Seven: Save the Old, Create New

Logs are usually kept for only a given period of time, then restarted.  Create a macro triggered by date, to move the current data to another workbook/worksheet, or creating a new workbook/worksheet while archiving the previous one.