Return-Path: <[EMAIL PROTECTED]>
Delivered-To: [EMAIL PROTECTED]
Delivered-To: [EMAIL PROTECTED]
Message-Id: <[EMAIL PROTECTED]>
Date: Wed, 09 Nov 2005 18:09:25 +0530
To: "program-l-freelists.org" <[EMAIL PROTECTED]>
From: Pranav Lal <[EMAIL PROTECTED]>
Subject: [program-l] Microsoft Excel: ensuring non blank values and
correct
totals
Mime-Version: 1.0
Content-Type: text/plain;
charset=us-ascii;
format=flowed
Precedence: normal
Hi all,
I am working on an Excel timesheet. The format of the
timesheet is as follows.
project_code, activity, activity_code, details,
columns for monday to Sunday that contain the hours spent by an
employee on a project
and a total column that totals every row.
The users are making the following errors;
1. A lot of them do not select the project and activity values in the
respective columns. These come from a drop down list which is at a
separate place on the sheet.
2. There is a problem with the total columns. If a user does a large
number of activities in a project, she needs to insert a row.
Many times, users do not copy the formula from the previous row so
the total does not come. Or, they enter the formula themselves in
which case they may not include all the days in the totals.
3. or they delete the total. My colleague who compiles the timesheet
is not too sure of the cause of the issue with the totals column.
I have written a macro to handle the first problem. The macro runs on
the Worksheet_SelectionChange event. I however, do not know how to
solve the incorrect totals problem. I had thought of broot forcing
the correct total by making a formula and adding it to the correct
cell. This approach is not working since I am getting a run time
error the text of which follows.
Microsoft Visual Basic
Run-time error '1004':
Method 'Range' of object '_Worksheet' failed
Continue End Debug Help
The code of my macro
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim col As Integer
Dim rw As Integer
Dim fr As String
col = Application.ActiveCell.Column
rw = Application.ActiveCell.Row
fr = "=sum(e" & rw & ":k" & rw & ")"
MsgBox frm
If Worksheets("timesheet").Cells(Application.ActiveCell.Row, 1).Value
= "" Then
Worksheets("timesheet").Cells(Application.ActiveCell.Row, 1).Select
Exit Sub
End If
If Worksheets("timesheet").Cells(Application.ActiveCell.Row, 2).Value
= "" Then
Worksheets("timesheet").Cells(Application.ActiveCell.Row, 2).Select
Exit Sub
End If
'ensure that the totals column is not blank
'this should only happen when the project and activity columns are
filled out
If Worksheets("timesheet").Cells(Application.ActiveCell.Row,
12).Value = "" And
Worksheets("timesheet").Cells(Application.ActiveCell.Row, 2).Value >
"" Then
Beep
Beep
Exit Sub
End If
Range(rw, 12).Formula = frm
End Sub
The error is coming on the following line
Range(rw, 12).Formula = frm
the frm variable should contain the constructed formula but the
string is
Also, how do I make a visual notification for the the situation where
the totals column is blank? I have an audable alert but that is not
effective with sited users.
Any one any thoughts?
Pranav
---------------------------------------------------------------------------------------------------------------------------------------
Pranav
--
----------------------------------------
WIN-HOME Archives: http://PEACH.EASE.LSOFT.COM/archives/WIN-HOME.html
Contact the List Owner about anything: [EMAIL PROTECTED]
Official Win-Home List Members Profiles Page
http://www.besteffort.com/winhome/Profiles.html
