Force users to Input hours on timecard

[Jerk24]

Good Morning.


I have a master workbook that is feed from employee time cards. what is to make sure that there are certain cells filled out when a code is entered into the following merged cells

If Range "Type" is not blank, corrisponding Range "Hours" the same row needs to have a number. i would like a message box to appear before this is saved to read (Please input the number of hours you did not work) ...

Also

to include the above rule I need to see if Range "Type" has the code "LM" my range "To" needs to have the word drill or a 6 digit number.

if not before it is saved i need a message to read ("Please input or order number for the day you used LM")

I will take the code and apply it to each time card.

I hope this is clear enough to get started.

 

[Caribeiro77]

You will need to give us some more information, about e.g. the layout of your spreadsheet, in what ranges are those values entered....
You can use Mr Excel HTML maker (if you don't know how to use it see the video on my signature how to do it)

 

[Jerk24]

Ok, my work computer will not allow me to use HTML maker,

the following are my named ranges (failed to mention that they were named)

Named Range "Type" is I10:L19 where I10:L10 is merged then I11:L11, I12:L12.....down to I19:L19

Named Range "Hours" M10:R10, down to M19:R19 each row is merged.

Named Range "To" S10:X10, down to MS19:X19... each row is merged.

 

[Caribeiro77]

Try to post an example of the spreadsheet when it's possible for you...

 

[Jerk24]

Yes, but all merged

I---L M-R S---------X[TABLE="width: 144"]
<tbody>[TR]
[TD="class: xl66, width: 48, bgcolor: yellow, colspan: 4"][/TD]
[TD="class: xl67, width: 72, bgcolor: yellow, colspan: 6"][/TD]
[TD="class: xl72, width: 72, bgcolor: yellow, colspan: 6"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: yellow, colspan: 4"][/TD]
[TD="class: xl67, bgcolor: yellow, colspan: 6"][/TD]
[TD="class: xl69, bgcolor: yellow, colspan: 6"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: yellow, colspan: 4"][/TD]
[TD="class: xl67, bgcolor: yellow, colspan: 6"][/TD]
[TD="class: xl69, bgcolor: yellow, colspan: 6"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: yellow, colspan: 4"][/TD]
[TD="class: xl67, bgcolor: yellow, colspan: 6"][/TD]
[TD="class: xl69, bgcolor: yellow, colspan: 6"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: yellow, colspan: 4"] LM
[/TD]
[TD="class: xl67, bgcolor: yellow, colspan: 6"]8
[/TD]
[TD="class: xl69, bgcolor: yellow, colspan: 6"]123456
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: yellow, colspan: 4"][/TD]
[TD="class: xl67, bgcolor: yellow, colspan: 6"][/TD]
[TD="class: xl69, bgcolor: yellow, colspan: 6"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: yellow, colspan: 4"]LA
[/TD]
[TD="class: xl67, bgcolor: yellow, colspan: 6"]8
[/TD]
[TD="class: xl69, bgcolor: yellow, colspan: 6"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: yellow, colspan: 4"][/TD]
[TD="class: xl67, bgcolor: yellow, colspan: 6"][/TD]
[TD="class: xl69, bgcolor: yellow, colspan: 6"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: yellow, colspan: 4"][/TD]
[TD="class: xl67, bgcolor: yellow, colspan: 6"][/TD]
[TD="class: xl69, bgcolor: yellow, colspan: 6"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: yellow, colspan: 4"]CE
[/TD]
[TD="class: xl67, bgcolor: yellow, colspan: 6"]8
[/TD]
[TD="class: xl69, bgcolor: yellow, colspan: 6"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Caribeiro77]

Ok i think i got it...Let me work on it....

 

[Jerk24]

Maybe this will help a bit?

http://www.mrexcel.com/forum/excel-...ill-another-specific-cell-before-closing.html

 

[Caribeiro77]

I'm shure that any of other VBA guru's arround here will provide you a much elegant code, i'm a bitt more than a begginer in VBA, even so i try to help with my little knowledge....
Try:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)


Dim lr As Integer


lr = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, "I").End(xlUp).Row


Cells(10, "I").Activate


Do Until ActiveCell = lr
If ActiveCell <> "" And ActiveCell.Offset(0, 1) = "" Then
MsgBox "Please input the number of hours you did not work in cell " & ActiveCell.Offset(0, 1).Address(, , xlA1)
ActiveCell.Offset(0, 1).Select
Cancel = True
Exit Sub
End If

If ActiveCell = "LM" And ActiveCell.Offset(0, 7) = "" Then
MsgBox "Please input or order number for the day you used LM " & ActiveCell.Offset(0, 7).Address(, , xlA1)
ActiveCell.Offset(0, 7).Select
Cancel = True
Exit Sub

End If

ActiveCell.Offset(1, 0).Select
If ActiveCell.Row > lr Then
Exit Sub
End If

Loop
End Sub

Note that you may have to change your sheet names.....in my code is "sheet1", change it to match with your sheet names...

 

[Jerk24]

Thank you for you help, this does not seem to be working. after I enter this into a Module and save it i do not get any message.

maybe I copied into the wrong module?

 

[Jerk24]

Would it make a diffence that I have the Named Ranges???