If then else

[jprogrammer]

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!

 

[dok112]

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]

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

 

[jprogrammer]

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!!!

 

[dok112]

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 A11 (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

 

[jprogrammer]

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

 

[jprogrammer]

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 |
+-------------------------------------------------------------------+

 

[glen.e.mettler]

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

 

[dok112]

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

 

[dok112]

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.

 

[jprogrammer]

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!!!

 

[dok112]

That code is a Macro, so by disabling it, your disabling the use of th
macro in the workbook. Thats the only way to make the code work. Now
you can go to each computer that will use the workbook, and change th
settings in Excel so it always accepts the Macros, however I wouldn'
recommend this b/c viruses can be written in macros. But, the only wa
to "force" the user to enter the data would be through a macro. If yo
have a problem with someone accidently selecting disable, then you ca
put into the Workbook_Open command, a macro that will not let someon
make changes to the file if they do not select "Enable Macros".
Basically writing a code that tells Excel to unlock the file once th
book opens and Macros are enabled. If the selected Disable Macros
then the workbook would be locked and no changes could be made to th
cells

 

[jprogrammer]

Basically writing a code that tells Excel to unlock the file once the
book opens and Macros are enabled. If the selected Disable Macros, then
the workbook would be locked and no changes could be made to the cells.

I see what you are saying. Well in order to make sure the user enters
the info needed, I think what you suggested in the quote above would be
a smart move.

How would I get started on writing this? I'll search the message
boards but if you know how would you let me know how to get started on
this?

Thanks!

 

[dok112]

Update your password you choose into the field between the "" "". Also.
You will need to add a line to the code we made earlier to "re-lock"
the file once it is closed. I'll put the locking code in another
message.

Private Sub Workbook_Open()
ThisWorkbook.Sheets("TimeCard").Unprotect "password for sheet goes
here"
End Sub

 

[dok112]

Here is the updated code to "re-lock" the fields when the file is
closing. If you dont want a password, but want the sheets still
locked, then you can just leave off the "password" portion
(WSN.Protect) (Unprotect), and it will lock the book without a
password. I also added a line to the end to force the book to be saved
so they dont have the option of closing out without saving. If you want
to re-instate that option, just remove that line from the code.

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
WSN.Protect "same password"
thisworkbook.close (true)

 

[jprogrammer]

Thank you so much! I'll take this home and give it try.

Have a great and safe weekend!

 

[jprogrammer]

Thank you so much! I'll take this home and give it try.

Have a great and safe weekend!

 

[jprogrammer]

Thank you so much! I'll take this home and give it try.

Have a great and safe weekend!

 

[jprogrammer]

Ok stupid question- I'll need to give each user the password to the
worksheet correct? I ask because now when I open the workbook, I'm
asked for a password.

Thanks!

 

[dok112]

It shouldn't be re-locking if they select Tardy & the field is blank.
We put the "Exit Sub" command in the If...Then... statement so it would
exit the macro and not re-lock. If it is re-locking, then resend the
book to me and I will look at the code. I don't have the book with me
to check the coding.