Data Validation

[Powlaz]

I asked for help on this before but I'm afraid the answer
was a little over my head. I have a timesheet set up.
If the user enters H or S or V in the very first column
he can not fill in his time card. The time will be
calculated based on the company's standard work day. I
used Data Validation to pop up an Error message if the
person tries.

The problem is, if the user fills in the day's hours
(c4,d4,e4,f4), and then returns to the first cell (b4)he
can enter H, S, or V without affecting the contents of
the other cells (c4-f4).

I need to be able to put a check on b4 so that if, at any
point, when that cell contains an H, S, or V data cannot
be entered into the next 4 cells and if the data is
already there it is erased.

One last thing. These 5 cells represent 1 day of the
week. So I need this method to be something that I can
easily apply to every day of the week. Each week ends in
a 3 row span of "Totals" so the days of the year are not
contiguous.

Any ideas? I tried the sample of code that was provided
last time but it didn't work for me and I don't know how
to troubleshoot it.

Thanks again, you guys have helped me alot.

Matt

 

[Frank Kabel]

Hi
one way to prevent data entry in cells C4-f4 would be to use data
validation with the following formula:
try the following data validation for B4:
=AND(C4<>"",D4<>"",E4<>"",F4<>"")
this will prevent changing the value in B4. Though the user has to
clear the values manually before entering data in B4. To automate this
you have to use VBA code (process the worksheet_change event).

Frank

 

[Powlaz]

Frank,
Thanks for the help. I like the solution, although I am
curious about automating the process. Do you have any
tips or a suggestion on how to do so?

Matt

 

[Frank Kabel]

Frank,
Thanks for the help. I like the solution, although I am
curious about automating the process. Do you have any
tips or a suggestion on how to do so?

Matt

Hi Matt
some ideas (just a little bit late for me to do the coding for this in
total, but you may get some ideas)
- In cell B4 use a normal data validation with a list for your allowed
values, validation for the other cells remain the same
- now to check for an entry in B4 AFTER the other cells have been
entered: one way would be to use the worksheet_change event. some
example code (not fully tested nor streamlined)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("B4")) Is Nothing Then Exit Sub '
Only cell B4 is checked
On Error GoTo CleanUp:
If Target.Offset(0,1)<>"" or Target.Offset(0,2)<>"" or
Target.Offset(0,3)<>"" then
msgbox "Values will be cleared"
Application.EnableEvents = False
Range("C4:E4").value = ""
end if
CleanUp:
Application.EnableEvents = True
End Sub

Frank

 

[David]

Frank Kabel wrote

On Error GoTo CleanUp:

Will this work with the colon (':')?

 

[Frank Kabel]

Frank Kabel wrote


Will this work with the colon (':')?

Hi David
seems to do Though i would delete the colon (Copy & Paste error...)

Frank

 

[Powlaz]

Thank you Frank! I should be exploring your ideas this
week.

Matt