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

[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 are going across the book, aka A1:D1 (looks for Tardy in these). It will continue to run until it hits a blank spot on the row it's looking for "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 in (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 Sub

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

[[email protected]]

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 the 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 the settings in Excel so it always accepts the Macros, however I wouldn't recommend this b/c viruses can be written in macros. But, the only way to "force" the user to enter the data would be through a macro. If you have a problem with someone accidently selecting disable, then you can put into the Workbook_Open command, a macro that will not let someone make changes to the file if they do not select "Enable Macros". 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.

[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]

This works great! Thanks!

I have a stupid question- when the user chooses "Enable Macros" , then chooses "Tardy" and tries to exit the workbook w/o adding how many minutes late the employee was, the cells then become locked. By doing this the user has to acquire the password from the "administrator". Is the correct?

Thanks for your help!

[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.