Message Box Help Please :)

[GSA_Tanya]

Hi, I need help and I hope someone is clever enough to un-jumble what I've written below and then help me sort out what I need

I've created a time sheet for my work. Within the time sheet I have created an IF formula to work out how many hours the person is supposed to work each day so that they can claim the difference as toil. Can someone please help me create a message box which will appear on each line as they enter their end of day time, to alert them that they have worked more or less hours than expected and need to claim the time?
My sheet is set up as follows: Title worksheet is Timesheet, range A7:U24
Column:
A: Day
B: Date (auto populated from lookup on separate tab)
C: Start Time
D: Start of Break 1 Time
E: Formula to calculate time between start of break 1, and start time.
F: Finish Time of Break 1
G: Formula to calculate total time of break 1
H: Start Break 2 Time
I: Formula to calculate the time between start break 2 and finish of break 1
J. Finish Break 2 Time
K. Finish Time for the day
L: Formula to calculate single break day to finish time
M: Formula to calculate time of break 2
N: Formula to calculate end of second break to end of day time
O: Formula ISText calculate total hours of day if 2 breaks were taken
P: Formula ISTEXT calculate total hours of single break day
Q: Formula ISTEXT calculate total hours worked if no break was taken
R: Daily Total : Formula lookup how many breaks and return appropriate value
S: Expected Hours: Formula - lookup employees name return expected hours from tab 2
T: Expected Hours: as Text (Wasn't sure if this is why my formula wasn't working so added in)
U: Claimable hours: Formula to calculate difference between expected and daily totals.

The columns with blue writing are all hidden.

The code I was previously advised to use is:

Private Sub Worksheet_Chane(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Offset(0, -2).Resize(1, 3) = "" Then Exit Sub
If Not Intersect(Target, Range("D:D) Is Nothing Then
If Target.Value > Target.Offset(0, -2).Value Then
MsgBox "You have exceeded the scheduled hours by " & Target.Offset(0, -1).Value & " hours."
ElseIf Target.Value < Target.Offset(0, -2).Value Then
MsgBox "You are short of the scheduled hours by " & Target.Offset(0, -1).Value & " hours."
End If
End If
End Sub

It's not working for me. If someone could please please please help me get it running that would be great!

 

[L. Howard]

The word Change is misspelled. May not be the only problem.

Howard

Private Sub Worksheet_Chane(ByVal Target As Range)

 

[GSA_Tanya]

thanks, still no luck. Does it need to say worksheet? Or should it say what my sheet is called? Which is Timesheet?

 

[L. Howard]

No, your sheet name is not needed in the sub heading.

The entire code needs to be in the sheet module of the sheet you want the action of the code to do it's thing.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
  'your code
End Sub


Investigate these lines that will exit the sub, are you taking care of those items on the sheet?

If Target.Cells.Count > 1 Then Exit Sub
If Target.Offset(0, -2).Resize(1, 3) = "" Then Exit Sub

If you are still having problems perhaps post a link to a sample workbook with an example sheet.

If I can't fix it some one else is sure to have an answer.

Howard

 

[GSA_Tanya]

Thanks Howard,

sorry to sound like a dunce but I didn't write that code another poster on here did. I don't understand what it's saying. And I don't know how to link this stuff to my spreadsheet for you to check but I'd love to send it to someone to play around with if you can help me out

 

[MARK858]

Obviously you retyped the code posted by JGLWhiz and didn't copy / paste it or the spelling mistake wouldn't be there. Can you try copying the code into your sheet module and re-running it then if it is still highlighted yellow can you let us know what error message you get.

For anyone interested a link to the previous thread is below
http://www.mrexcel.com/forum/excel-questions/746371-macro-message-box-help.html#post3668397

To post a link to your file...
Upload your file to a free file hosting site like Box.com, remember to mark it for sharing and post the link it provides in the thread.

 

[jsotola]

i pasted your code into excel

and got this

Private Sub Worksheet_Chane(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Offset(0, -2).Resize(1, 3) = "" Then Exit Sub
[COLOR=#ff0000]If Not Intersect(Target, Range("D:D) Is Nothing Then[/COLOR]
If Target.Value > Target.Offset(0, -2).Value Then
MsgBox "You have exceeded the scheduled hours by " & Target.Offset(0, -1).Value & " hours."
ElseIf Target.Value < Target.Offset(0, -2).Value Then
MsgBox "You are short of the scheduled hours by " & Target.Offset(0, -1).Value & " hours."
End If
End If
End Sub

excel highlighted the fourth line in red

that means that it has an unrecognised command

----------------------------------------------------------------

this is your code rewritten

i highlighted the correction in red

this code only runs when you update column D

that is probably what you do not want

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Cells.Count > 1 Then Exit Sub                 ' exit if updating more than one cell
    If Target.Offset(0, -2).Resize(1, 3) = "" Then Exit Sub
    
    If Not Intersect(Target, Range("D:D[COLOR=#ff0000]")[/COLOR]) Is Nothing Then  ' updating column D
    
        If Target.Value > Target.Offset(0, -2).Value Then
            
            MsgBox "You have exceeded the scheduled hours by " & Target.Offset(0, -1).Value & " hours."
        
        ElseIf Target.Value < Target.Offset(0, -2).Value Then
            
            MsgBox "You are short of the scheduled hours by " & Target.Offset(0, -1).Value & " hours."
        
        End If
    End If
End Sub

in reading your description of your worksheet columns, it appears to me that you do not have it clearly in your mind how the data flows from column to column

may i suggest regrouping your columns as follows

the layout puts start and stop times together
also break times are grouped and work times are grouped

why are you distinguishing between number of breaks

you do not really need all those columns that calculate intervals between most of the events

no need to calculate dayStart > break1Start > break1End > break2Start > Break2End > dayEnd

you only need time interval from dayStart to dayEnd

totalBreakTime = break1Time + break2Time

then subtract totalBreakTime to get totalWorkTime

A: Day
B: Date (auto populated from lookup on separate tab)
---------------------------------------------------------------------------
data input columns


C: Start  Time of Day


D: Start  Time of Break 1
E: Finish Time of Break 1


F: Start  Time of Break 2
G. Finish Time of Break 2


H. Finish Time of Day
----------------------------------------------------------------------------

need these two (could be put in one column)

I: Formula to calculate total time of break 1
J: Formula to calculate total time of break 2 
----------------------------------------------------------------------------
don't really need these


K: Formula to calculate the time between start time and start of break 1
L: Formula to calculate the time between finish of break 1 and start break 2
M: Formula to calculate the time between finish of break 2 and end of day


N: Formula to calculate the time between finish of break 1 and end of day ( single break day )



O: Formula ISText calculate total hours worked if  2 breaks were taken
P: Formula ISText calculate total hours worked if  1 break  was  taken
Q: Formula ISText calculate total hours worked if no break  was  taken


----------------------------------------------------------------------------


just calculate total work time using col C, H, I, J 
R: H-C-I-J

R: Daily Total : Formula lookup how many breaks and return appropriate value
S: Expected Hours: Formula - lookup employees name return expected hours from tab 2
T: Expected Hours: as Text (Wasn't sure if this is why my formula wasn't working so added in)
U: Claimable hours: Formula to calculate difference between expected and daily totals.

if you want to keep all your columns in the layout that you have, then please post a confirmation
and we will come up with code that will work for you

 

[mikerickson]

I'm surprised that this line doesn't cause a type mismatch error.

If Target.Offset(0, -2).Resize(1, 3) = "" Then Exit Sub

 

[GSA_Tanya]

Hi Jstola,

I have changed the layout to the sheet with your suggestions. It is now as follows:
A8: Day
B: Date
C: Start Time
D: Start Break 1
E: Finish Break 1
F: Start Break 2
G: Finish Break 2
H: Finish Time
I: Daily Total
J: Expected daily total (pulled though from separate tab)
K: Claimable hours (formula J-I)
L: Drop down list
M: Toil = Total of claimable hours for the day if selected Toil from column L
N: Flexi Time = Total of claimable hours for the day if selected Toil from column L
O: Overtime = Total of claimable hours for the day if selected Toil from column L
P: A/L = Total from column I if selected Annual Leave in column L
Q: P/L = Total from Column I if selected Personal Leave in Column L
R: C/L= Total from Column I if selected Compassionate Leave from Column L

- so if you could work your macro magic on this layout that would be greatly appreciated.

 

[GSA_Tanya]

Total area of sheet is A8:R21 (totals are in row 22).
Also is it possible to have a command button that submits the timesheet to the appropriate manager with an IF lookup perhaps? We're a paperless office so to avoid printing many time sheets out i'm looking for a solution to allow the process to get through to Payroll without printing.