ExcelBanter - MsgBox

bumper338,

If you mean that the user will always specify whether they want the extra
hours are Overtime or Comptime then you will not need the second messagebox,
(or what would happen if you selected vbNo for each messagebox?)

I would also think that you would be better using the target cell reference
rather than hard coding the cell reference. In the code below I assume that
you are entering data into Columns K, N, Q, T, W, Z & AC and entering the
OT/Comp in the following column. Something like:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim pintAnswer As Integer
Dim aRow As Long
Dim ACol As Integer
Dim bCol As Integer

If (Target.Column - 11) Mod 3 < 0 Then Exit Sub

Application.EnableEvents = False

aRow = Target.Row
ACol = Target.Column
bCol = ACol + 1

If Cells(aRow, ACol).Value 8 And Cells(aRow, bCol).Value = "" Then

pintAnswer = _
MsgBox("Do you want to be paid Overtime for the additional" & _
" hour(s) that you worked today?", vbYesNo + _
vbQuestion, "Overtime Time sheet")

If pintAnswer = vbYes Then
Cells(aRow, bCol) = "OT"
Else
Cells(aRow, bCol) = "Comp"
End If
End If
Application.EnableEvents = True

End Sub

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

with @tiscali.co.uk

"bumper338" wrote in message
...
I have created a timesheet that when a user completes any day and the total
hours for the day exceeds 8 hours they get the option of overtime or
comptime. However, I need to also add a sum function that shows total
hours
over 40 that the user has not specified if the hours will be overtime or
comptime. For example if Monday through Friday the user worked 9 hours,
each
day the person will identify the additional hour as overtime or comptime,
but
if they work 3 hours on Saturday I need to ask if these hours will be
overtime or comptime. Below is what I have so far, but I can not figure
out
the rest.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim pintAnswer As Integer

Dim r As New Worksheet

Set r = Sheet55

'This is for Week 1 Monday
If r.Cells(13, 11) 8 And r.Cells(13, 12) = "" Then

pintAnswer = MsgBox("Do you want to be paid Overtime for the
additional hour(s) that you worked today?", vbYesNo + vbQuestion, "Monday
Week 1 Overtime")

If pintAnswer = vbYes Then
r.Cells(13, 12) = "OT"
Else
pintAnswer2 = MsgBox("Do you want to earn Comp Time Hours
for the additional hour(s) worked today?", vbYesNo + vbQuestion, "Monday
Week
1 Comp Time")

If pintAnswer2 = vbYes Then
r.Cells(13, 12) = "Comp"
End If
End If
End If

Any suggestions would be great.

Thanks