I'm trying to make a time-keeping worksheet with clock-in and clock-out times (example attached). I'm using vba to alert the user if the clock out time is earlier than the start time. My code works...sort of
What I'd really like to add to this is a loop that won't let you move on until you've fixed the problem. In other words, it won't let any values in column H remain negative. Does that make sense?
Please Login or Register to view this content.
Thanks!!
I suggest to use the Data Validation feature.
On Sheet1, select E3:E10 (or the end-time data cells)
On the menu select Data\ ValidationAllow: Time
Data: greater than
Start time =$C3
On the Error alert tab, configure your message.
The user won't be allowed to enter a time less than the start times.
Surround your VBA code with CODE tags e.g.;
[CODE]your VBA code here[/CODE]
The # button in the forum editor will apply CODE tags around your selected text.
AlphaFrog, that accomplishes part of what I'm wanting. But with that DV rule, I can't have a dropdown list of times. My users would have to hard enter the time, and I don't want that.
I know you can combine DV rules into a custom formula, but I don't know of a way to combine a formula with a list.
This DV formula will create a DV Dropdown list where the times in the list are all after the start time in column C.
=OFFSET($M$1,MATCH($C3,$M:$M,1),0,COUNT($M:$M)-MATCH($C3,$M:$M,1))
The list of all times in order from earliest to latest is in column M starting at M1 .
Alpha, I'm a little confused, do I put that formula in the DV custom formula? And can I use the same list of times that my first column is referring to?
Either way it's not working. The clock-out column doesn't have a dropdown and the error is buggy.
Is there a way to do this with VBA?
Yes and YesOriginally Posted by phelbin
What is the range that has the list of times? Substitute that range for M:M is the formula.
Perhaps the example workbook could have been more representative of your actual workbook. There were no DV lists in the example.Either way it's not working. The clock-out column doesn't have a dropdown and the error is buggy
Certainly you could use VBA, but I think this is a more elegant solution.Is there a way to do this with VBA?
The sample file does have a DV list...it's on Sheet2. And columns C and E are both referring to it.
I just downloaded the example workbook again and neither sheets 1 or 2 have DV lists.
In my example workbook, Sheet1 C3:C10, when I bring up the DV dialog box, it has list with the source 'Sheet2'!A1:A97.
As far as I can tell it's working because those cells have dropdowns with the data on Sheet2.
Perhaps there's something I don't know about how DV works? Does a list have to be designated as such?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks