Time Difference - Post reconciliation Check

[Pankaj]

Greetings,

I have 3 columns in a worksheet.

Col A: Shows time information as a string

Col B: Some task that need to be performed based on time information
provided in Col A

Col C: This is entered manually to tell when the task was actually
performed.

Example:

Col A Col B Col C (Updated time)
5:00 PM Do something 5:20 PM

There is a requirement that whenever the time difference between Col A
and Col C is more than 30 mins, user should be prompted up an
message showing where the discrepany is when he tries to either close
or save the worksheet. He should not be allowed to save/close
worksheet until this is corrected

Can anyone please let me know if this can be done in a macro. I am
currently using Microsoft Excel 2003.

Happy New Year in advance
TIA

 

[Chip Pearson]

In the ThisWorkbook code module, use the following code. Change the
line marked with <<<< to the cell where the testing should begin. Each
row will be tested until an empty cell is encountered in column A. If
an error is found, a message is displayed and the Close operation is
cancelled.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim R As Range
Dim S As String
Set R = Worksheets("Sheet2").Range("A1") '<<<<<
Do Until R.Value = vbNullString
If R.EntireRow.Cells(1, "C").Value - _
R.EntireRow.Cells(1, "A").Value > TimeSerial(0, 30, 0) Then
S = S + "bad task in row: " & CStr(R.Row) & vbNewLine
End If
Set R = R(2, 1)
Loop
If Len(S) > 0 Then
MsgBox S, vbOKOnly
Cancel = True
End If
End Sub


Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

 

[Pankaj]

In the ThisWorkbook code module, use the following code. Change the
line marked with <<<< to the cell where the testing should begin. Each
row will be tested until an empty cell is encountered in column A. If
an error is found, a message is displayed and the Close operation is
cancelled.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim R As Range
Dim S As String
Set R = Worksheets("Sheet2").Range("A1") '<<<<<
Do Until R.Value = vbNullString
    If R.EntireRow.Cells(1, "C").Value - _
        R.EntireRow.Cells(1, "A").Value > TimeSerial(0, 30, 0) Then
        S = S + "bad task in row: " & CStr(R.Row) & vbNewLine
    End If
    Set R = R(2, 1)
Loop
If Len(S) > 0 Then
    MsgBox S, vbOKOnly
    Cancel = True
End If
End Sub

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLCwww.cpearson.com
[email on web site]

Greetings,

I have 3 columns in a worksheet.

Col A: Shows time information as a string

Col B: Some task that need to be performed based on time information
provided in Col A

Col C: This is entered manually to tell when the task was actually
performed.

Col A            Col B                   Col C (Updated time)
5:00 PM         Do something       5:20 PM

There is a requirement that whenever the time difference between Col A
and Col C is  more than 30  mins, user should be prompted up an
message showing where the discrepany is when he tries to either close
or save the worksheet. He should not be allowed to save/close
worksheet until this is corrected

Can anyone please let me know if this can be done in a macro. I am
currently using Microsoft Excel 2003.

Happy New Year in advance
TIA- Hide quoted text -

- Show quoted text -

Thanks Chip. One information that seems to be missing in my orignal
requirement was the format of date used. The col A shows only time
like 7:30 PM but in Col C, we have information along with date as well
(eg: 31/Dec/2009 7:30 PM).

Do we need to alter our current code to reflect this. Sorry for the
missing piece.

TIA

 

[Chip Pearson]

Change

to

If (R.EntireRow.Cells(1, "C").Value - _
Int(R.EntireRow.Cells(1, "C").Value)) - _
(R.EntireRow.Cells(1, "A").Value - _
Int(R.EntireRow.Cells(1, "A").Value)) > TimeSerial(0, 30, 0)
Then



Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

In the ThisWorkbook code module, use the following code. Change the
line marked with <<<< to the cell where the testing should begin. Each
row will be tested until an empty cell is encountered in column A. If
an error is found, a message is displayed and the Close operation is
cancelled.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim R As Range
Dim S As String
Set R = Worksheets("Sheet2").Range("A1") '<<<<<
Do Until R.Value = vbNullString
    If R.EntireRow.Cells(1, "C").Value - _
        R.EntireRow.Cells(1, "A").Value > TimeSerial(0, 30, 0) Then
        S = S + "bad task in row: " & CStr(R.Row) & vbNewLine
    End If
    Set R = R(2, 1)
Loop
If Len(S) > 0 Then
    MsgBox S, vbOKOnly
    Cancel = True
End If
End Sub

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLCwww.cpearson.com
[email on web site]

Greetings,

I have 3 columns in a worksheet.

Col A: Shows time information as a string

Col B: Some task that need to be performed based on time information
provided in Col A

Col C: This is entered manually to tell when the task was actually
performed.

Col A            Col B                   Col C (Updated time)
5:00 PM         Do something       5:20 PM

There is a requirement that whenever the time difference between Col A
and Col C is  more than 30  mins, user should be prompted up an
message showing where the discrepany is when he tries to either close
or save the worksheet. He should not be allowed to save/close
worksheet until this is corrected

Can anyone please let me know if this can be done in a macro. I am
currently using Microsoft Excel 2003.

Happy New Year in advance
TIA- Hide quoted text -

- Show quoted text -

Thanks Chip. One information that seems to be missing in my orignal
requirement was the format of date used. The col A shows only time
like 7:30 PM but in Col C, we have information along with date as well
(eg: 31/Dec/2009 7:30 PM).

Do we need to alter our current code to reflect this. Sorry for the
missing piece.

TIA

 

[Pankaj]

Change

to

If (R.EntireRow.Cells(1, "C").Value - _
        Int(R.EntireRow.Cells(1, "C").Value)) - _
    (R.EntireRow.Cells(1, "A").Value - _
        Int(R.EntireRow.Cells(1, "A").Value)) > TimeSerial(0, 30,0)
Then

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLCwww.cpearson.com
[email on web site]

In the ThisWorkbook code module, use the following code. Change the
line marked with <<<< to the cell where the testing should begin. Each
row will be tested until an empty cell is encountered in column A. If
an error is found, a message is displayed and the Close operation is
cancelled.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim R As Range
Dim S As String
Set R = Worksheets("Sheet2").Range("A1") '<<<<<
Do Until R.Value = vbNullString
    If R.EntireRow.Cells(1, "C").Value - _
        R.EntireRow.Cells(1, "A").Value > TimeSerial(0, 30, 0)Then
        S = S + "bad task in row: " & CStr(R.Row) & vbNewLine
    End If
    Set R = R(2, 1)
Loop
If Len(S) > 0 Then
    MsgBox S, vbOKOnly
    Cancel = True
End If
End Sub
Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLCwww.cpearson.com
[email on web site]
On Thu, 31 Dec 2009 10:36:01 -0800 (PST), Pankaj
Greetings,
I have 3 columns in a worksheet.
Col A: Shows time information as a string
Col B: Some task that need to be performed based on time information
provided in Col A
Col C: This is entered manually to tell when the task was actually
performed.
Example:
Col A            Col B                   Col C (Updated time)
5:00 PM         Do something       5:20 PM
There is a requirement that whenever the time difference between Col A
and Col C is  more than 30  mins, user should be prompted up an
message showing where the discrepany is when he tries to either close
or save the worksheet. He should not be allowed to save/close
worksheet until this is corrected
Can anyone please let me know if this can be done in a macro. I am
currently using Microsoft Excel 2003.
Happy New Year in advance
TIA- Hide quoted text -
- Show quoted text -

Thanks Chip. One information that seems to be missing in my orignal
requirement was the format of date used. The col A shows only time
like 7:30 PM but in Col C, we have information along with date as well
(eg: 31/Dec/2009 7:30 PM).

Do we need to alter our current code to reflect this. Sorry for the
missing piece.

TIA- Hide quoted text -

- Show quoted text -

Thanks Chip. I tried but it gave me "Runtime error 13: Type mismatch"
error. Is the Int function needs to be changed here?