Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation
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 wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation
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 -----Original Message----- 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 wrote: 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation
Powlaz wrote:
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation
Frank Kabel wrote
On Error GoTo CleanUp: Will this work with the colon (':')? -- David |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation
David wrote:
Frank Kabel wrote On Error GoTo CleanUp: Will this work with the colon (':')? Hi David seems to do :-) Though i would delete the colon (Copy & Paste error...) Frank |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation
Thank you Frank! I should be exploring your ideas this
week. Matt -----Original Message----- David wrote: Frank Kabel wrote On Error GoTo CleanUp: Will this work with the colon (':')? Hi David seems to do :-) Though i would delete the colon (Copy & Paste error...) Frank . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
custom data validation on cells with data validation values | Excel Worksheet Functions | |||
Validation Data using Validation Table cell range..... | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Worksheet Functions | |||
Data validation with validation lists and combo boxs | Excel Discussion (Misc queries) |