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"

Reference:
http://www.exceltip.com
/st/Add_Combo_Box_to_a_Sheet_in_Microsoft_Excel/300.html

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
Reference:
http://www.fontstuff.com/vba/vbatut03.htm
http://www.fontstuff.com/vba/vbatut07.htm
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_vsto2005_ta/html/OfficeVSTODynamicControls.asp

0 Comments:

Post a Comment

<< Home