## Step Creating a Timesheet Spreadsheet

Vertex42 The Excel Nexus

Get Instant Access

Now is the time to set up the user interface for your web application (Figure 17-34). Because most users are familiar with the spreadsheet format, they will welcome the idea of keeping track of their time in a worksheet, especially if you make it quick and easy for them to use.

1. Create a worksheet, as shown in Figure 17-34 above. Use the following guidelines to place formulas:

■ Each day of the week has a corresponding date that is calculated, as shown below:

Mon =IF(C5<>"",\$C\$5-6,"") Tue =IF(\$C\$5<>"",C9+1,"") Wed =IF(\$C\$5<>"",C10+1,"") Thu =IF(\$C\$5<>"",C11 + 1,"") Fri =IF(\$C\$5<>"",C12+1,"") Sat =IF(\$C\$5<>"",C13+1,"") Sun =IF(\$C\$5<>"",C14+1,"")

■ Use the SUM function to calculate totals in the Regular Hours and Overtime columns:

Total For Week =SUM(D9:D15) =SUM(E9:E15)

■ Place the formulas that calculate week-ending dates, as shown in Figure 17-35. Notice that these formulas are out of view when the spreadsheet is first presented to the user. The formulas assume that Sunday is the last day of the week.

Figure 17-34:

This custom Timesheet worksheet will allow users to report their time to the central server for further processing.

Figure 17-34:

This custom Timesheet worksheet will allow users to report their time to the central server for further processing.

 - III:ill> <:»: 1 nit:'hit'xls BEI B1 c 1 D -—-* 26 =C29-7 =C30-7 =C31-7 31 |=TOD AY 0-WE EKDA YfTOD AY 0,2) 1 =C32+7 =C33+7 =C34+7 =C35+7 =C36+7 33 H < ► wf\Tinïesheel /¡Jbeutt /jJiJ |

Figure 17-35:

Week-ending dates are based on simple formulas.

Figure 17-35:

Week-ending dates are based on simple formulas.

Link cell C5 to Week Ending Dates in cells C28:C37 as follows: Select cell C5. Choose Validation from the Data menu. The Data Validation dialog box will appear. Fill in the Data Validation dialog, as shown in Figure 17-36.

In the Data Validation dialog box, click the Input Message tab and type the following input message: Select Week Ending Date. Make sure to mark the Show input message when cell is selected check box. In the Data Validation dialog box, click the Error Alert tab and fill it out as shown in Figure 17-37.

Figure 17-36: The list setting in the Data Validation dialog box allows you to create a dropdown list that gets its choices from cells elsewhere on the worksheet.

Figure 17-37: Use the Error Alert tab in the Data Validation dialog box to create a message that appears when incorrect data has been entered.

Click OK to exit the Data Validation dialog box.

3. Use the Define Name dialog box (Figure 17-38) to assign the following names to spreadsheet cells:

 Name Refers to CalDate =Timesheet \$C\$9:\$C\$15 DayOfWeek =Timesheet \$B\$9:\$B\$15 EmployeeName =Timesheet \$C\$4 EMPLOYEETIMESHEET =Timesheet \$B\$2:\$E\$16 EndingDate =Timesheet \$C\$5 Overtime =Timesheet \$E\$9:\$E\$15 RegularHrs =Timesheet \$D\$9:\$D\$15 TotalOvertime =Timesheet \$E\$16 TotalRegularHrs =Timesheet \$D\$16

Define Name

Bta\

Names in workbook:

1 OK 1

CalDate

T1

DayOfWeek EmployeeName

OoSS

EMPLOYEETIME5HEET EndingDate

Overtime RegularHrs TotalOvertime TotalRegularHrs

Delete

m

Refers to:

j-Timesheetl\$C\$S

S

Use this dialog box to define names in the Timesheet spreadsheet.

Figure 17-38:

Use this dialog box to define names in the Timesheet spreadsheet.

4. Place three command buttons on the worksheet using the Control Toolbox: Choose View | Toolbars | Control Toolbox to display the Control Toolbox.

Figure 17-39:

Click the Command Button tool in the Control Toolbox and click in cell E18 to place a command button on the worksheet. Right-click the button and choose Properties from the shortcut menu. Fill in the properties for the Submit button, as shown in Figure 17-40.

Using the same method, place the other two buttons (Clear and MyTimesheets) on the worksheet. Assign cmdClear and cmdMy-Time to the Name properties of these buttons.

Figure 17-39:

Right-click each button and choose View Code from the shortcut menu. You will get skeletons for your VBA procedures in the Sheet1 (Timesheet) Code window:

Private Sub cmdClear_Click() End Sub

Private Sub cmdClear_Click() End Sub

Private Sub cmdClear_Click() End Sub

|cmd5ubmit Cùrfimand&ijttcin - !

Alphabetic | Caregoived ]

|cmd5ubmit Cùrfimand&ijttcin - !

Alphabetic | Caregoived ]

 (Narne) cmdSubmit Accelerator S AltHTML AutoLoad False AutoSize False _▼] BackColor □ &H000040C0& BackStyle 1 - fmBackStyleOpaque Caption Submit Enabled True Font Arial ForeColor □ &H00FFFFFF& Height 25.5 Left 287.25 Locked True Mouselcon (None) MousePointer 0 - f m MousePointer Default Picture (None) PicturePosition 7 - fmPicturePositionAboveCenter Placement 2 PrintObject True Shadow False TakeFocusOnClick True Top 243.75 Visible True Width 53.25 WordWrap The Properties sheet lists all properties that apply to the selected control. Figure 17-40: The Properties sheet lists all properties that apply to the selected control.