Step One: Gather some information
- Who will be entering information
- 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.
- Who will be using the information. This will drive sorting
- What will be using this information. (including other excel reports, Word documents, will be using the output of this
- What the current activity log entails, especially what content is normally gathered
- Other Excel worksheets they use for logging calls or visitors.
Step Two: Design and Create the Initial Worksheet
- 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:
- Build the worksheet with only the columns you are certain are needed.
- 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
- Format headers like other reports you've seen used by the client
- Bring to the review suggestions about other columns that might be added.
- Keep expectations low as to the sophistication of what will be coming down the pike. Avoid showing drop-downs, automated formatting, etc
- Ask for suggestions on formatting the headers
- Review with the user
Step Three: Review and Refine the Worksheet
- Type in the time the call came in
- Type in who called
- Type in what was said/decided during the call
- Document what was decided and what was requested
- Send the client a copy to confirm you know what is going on and to give them one more chance to think about it
- 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:
- 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".
Public Const ActLog_dblTimeStampColNum As Double = 1
- Often what could be a global constant is broken into a constant and a variable.
Public Const DateFormat_yyyy_mm_dd_hh_mm_ss as String = "yyyy-mm-dd hh:mm:ss"
Public ActLog_strTimeStampFormat as String
Date/Time Stamp Code
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
- Create modules for Global Constants and Global Variables and name them accordingly
- Add the definitions for the Global Constants and Global Variables
- 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.
- Enter the code from above in the Workbook_Open event Sub
- 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.
- 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
- Add the code from above
- Test and refine
Step Three: Control the Input Fields
- 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
- 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