Friday, June 24, 2005

Spread sheet template for daily work log

I like using excel 2003 for my documentation, and I just want to build a template for my daily work log, I want to do following:
1. Design a table for work log sheet (basical function of Excel)
2. Add some control boxes in the worksheet, such as Combo box to select common data for some cells.
Step 1: Define a list of selection for Combobox
Write down all the items in one column, select them all --> Ctrl + F3 --> Define a name in workbook
Step 2: Open the form toolbar
Select one of the toolbars, right-click, and select the Forms toolbar
Step 3: Add a combo box to a worksheet from Forms toolbar
To fit the cell size, hold down ALT key while adjusting the size of Combo Box
Step 4: Add input range for combo box
right-click the combo box--> Format contrl --> Control --> Input Range --> input the name in workbook that defined in step 1 --> ok for confirmation
Note: if don't want this combo box to be printed out, just uncheck the "print object" option in "Format control-->Properties"


3. Write a macro for automatically add a work sheet for daily work log and fill in some definite information such as name and date.
Step 1: Open VBA to build an Excel add-in
"Tools"-->"Macro"-->"Visual Basic Editor"
Step 2: Form design/Coding
In the VBA project, the Excel Objects is the excel file which contains the worksheets and ThisWorkBook.
Write code for a module to copy a work log template from the first worksheet to a new worksheet named by today's date.
Write code for another module to date/time picker, which used forms design.(need add forms in VBAproject)
Step 3: Add buttons for the macro on toolbar
"Tools" --> "Customize"-->"Command" tab-->"Macro"--> drag the "customer button" to the toolbar


Post a Comment

<< Home