I have a spreadsheet that tracks time sheet information.
I have a cell (A2) that has a drop down list with 2 options- "Tardy" OR
"On-Time". I want to be able to make cell A3 mandatory that you fill in
how many minutes the person was late if A2 is = "Tardy".
So If A2 = "Tardy"
Then A3 Is required
Else
Do Nothing.

Can this be done?

Thanks for any direction!


--
jprogrammer
------------------------------------------------------------------------
jprogrammer's Profile: http://www.excelforum.com/member.php...o&userid=29781
View this thread: http://www.excelforum.com/showthread...hreadid=513772

You can't force it since the formula would fall within that space. What
you can do, is make a statement like below which will appear if Tardy is
selected.

Put the below formula in cell A3:

=IF(A1="Tardy", "***Please enter the amount of time late in cell
A2***", "")

This will make "***Please enter the amount of time late in cell A2***"
appear in A3 each time Tardy is selected. or you can word the message
differently.


--
dok112
------------------------------------------------------------------------
dok112's Profile: http://www.excelforum.com/member.php...o&userid=10581
View this thread: http://www.excelforum.com/showthread...hreadid=513772

Now, you can use a loop statement when the file is closed to check t
see if A2 has been filled in if A1 says "Tardy" before you will b
allowed to exit the file. If you want something like that, let m
know

--
dok11
-----------------------------------------------------------------------
dok112's Profile: http://www.excelforum.com/member.php...fo&userid=1058
View this thread: http://www.excelforum.com/showthread.php?threadid=51377

Now, you can use a loop statement when the file is closed to check to
see if A2 has been filled in if A1 says "Tardy" before you will be
allowed to exit the file. If you want something like that, let me know.

That would be great actually. *THANKS* We are trying to "make" the user
enter how many minutes the person was Tardy. If not we will have
incomplete reports.

Thanks so much!!!


--
jprogrammer
------------------------------------------------------------------------
jprogrammer's Profile: http://www.excelforum.com/member.php...o&userid=29781
View this thread: http://www.excelforum.com/showthread...hreadid=513772

This will work if you put the below code in the MS Visual Basic Editor.
It needs to go in the "This Workbook" object. This works if you ar
going across the book, aka A1:D1 (looks for Tardy in these). It wil
continue to run until it hits a blank spot on the row it's looking fo
"Tardy" on. If you need something different let me know...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim r As Variant
Dim WSN As Variant
Set WSN = ThisWorkbook.Sheets("Sheet1") 'change to sheet to run on
r = 1 'change this to the value of the first cell to start the loop i
(if A2 then r = 2)
Do Until WSN.Cells(1, r).Value = ""

If WSN.Cells(1, r).Value = "Tardy" And WSN.Cells(2, r).Value = "
Then
MsgBox "Please enter the amount of time tardy."
Cancel = True
Exit Do
Exit Sub
Else:
r = r + 1
End If
Loop
End Su

--
dok11
-----------------------------------------------------------------------
dok112's Profile: http://www.excelforum.com/member.php...fo&userid=1058
View this thread: http://www.excelforum.com/showthread.php?threadid=51377

I can't get it to work. Can I post my workbook somewhere so you ca
what knuckle head mistake I've made?

Thanks so much

--
jprogramme
-----------------------------------------------------------------------
jprogrammer's Profile: http://www.excelforum.com/member.php...fo&userid=2978
View this thread: http://www.excelforum.com/showthread.php?threadid=51377

I can't get it work for some reason.

I posted my workbook so you can look at it IF you have time to see what
bone head mistake I may have made.

Thanks again!


+-------------------------------------------------------------------+
|Filename: Book1.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4372 |
+-------------------------------------------------------------------+

--
jprogrammer
------------------------------------------------------------------------
jprogrammer's Profile: http://www.excelforum.com/member.php...o&userid=29781
View this thread: http://www.excelforum.com/showthread...hreadid=513772

Another option would be to enter the time of arrival for each person
and let the spreadsheet calculate the number of minutes "Tardy" against
the default time.

Glen

let me look at it and see what I can come up with

--
dok11
-----------------------------------------------------------------------
dok112's Profile: http://www.excelforum.com/member.php...fo&userid=1058
View this thread: http://www.excelforum.com/showthread.php?threadid=51377

Replace the code with this...

Dim r As Variant
Dim WSN As Variant
Set WSN = ThisWorkbook.Sheets("TimeCard")
r = 9
Do Until WSN.Cells(r, 10).Value = ""
If WSN.Cells(r, 10).Value = "Tardy" And WSN.Cells(r, 11).Value = ""
Then
MsgBox "Please enter the amount of time tardy"
Cancel = True
Exit Do
Exit Sub
Else:
r = r + 1
End If
Loop

This should work for you now. I tested it on your book, and it works.
I just had to switch the code around a little.


--
dok112
------------------------------------------------------------------------
dok112's Profile: http://www.excelforum.com/member.php...o&userid=10581
View this thread: http://www.excelforum.com/showthread...hreadid=513772

Thank you so much dok112!!!

One question, if I tell Excel to "Disable Macros" when it opens the
workbook, the code doesn't work. Is there a way around this?

Thanks so much again!!!


--
jprogrammer
------------------------------------------------------------------------
jprogrammer's Profile: http://www.excelforum.com/member.php...o&userid=29781
View this thread: http://www.excelforum.com/showthread...hreadid=513772