Macro to reset dropdown at a given system time 1

[Benaway]

I currently have a spreadsheet that is used as a check in/out board at work. Attaching a small sample of it. What I want to do is for all persons that are currently checked in thru a dropdown box, and forget to check out at the end of the day, a macro could be set up to run and reset all of the check in to checkout's, but leaving all other dropdown choices alone, including vacation, sick, etc. Ideally, would like this to automatically work from a given system time such as 11:59 PM, and would like this to work weather the file is open or not. If this can not be down, option #2 would be to have a macro button that when clicked, it would do the same thing above. In this way, we would not have to manually reset all the in's to outs every morning for some 80 employees.

 

[SkipVought]

Hi,

So what you really want is that the first time that the workbook is opened for any day, that all the Ins would be changed to Outs. The time is actually irrelevant.

Yes?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Benaway]

That is correct, that would work perfect for us.....

 

[SkipVought]

In Sheet2, enter
[tt]
PrevDate
[/tt]
...in A1, SELECT A1:A2 and Formulas > Defined names > Create from selection ... Create names from values in the TOP row. This creates a Named Range that the code will use to test/record a date.

I also converted your table to a Structured Table named tInOut. Your workbook is attached.

Paste this code into your ThisWorkbook Object code sheet.

Sub ResetInOut()
    Dim r As Range
    Const IN_ = "In"
    Const OUT_ = "Out"
    
    For Each r In [tInOut[Status]]
        If r.Value = IN_ Then
            r.Value = OUT_
        End If
    Next
End Sub

Private Sub Workbook_Open()
    If [PrevDate] <> Date Then
        ResetInOut
        [PrevDate] = Date
    End If
End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Benaway]

Really appreciate your help. Evidently, I am amiss of something. I place the micro into our spreadsheet that we will be using and am getting an error. I attached the actually file that is currently being used, could you please check it for me and correct it, then maybe I can see what I did wrong. (Stupid me) Once this is done, we will be adding about 30 more names to it. You are a lifesaver, thanks in advance.

 

[SkipVought]

1) You never Named the PrevDate range as per my instructions.

2) You never made your In/Out table a Structured Table via Insert > Tables > Table

3) You have TWO Workbook_Open events in ThisWorkbook code sheet, which results in a compile error - ambiguous name.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Here's your corrected workbook.

BTW, when you add rows to your In/Out table (noe a Structured Table), the range references for each column automatically adjusts: a GREAT feature!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Benaway]

Thank you for the help. Was not sure how to do the structure table, but I finally figured it out. Some of this was really new to me. Again a big thx....

 

[Benaway]

OK, thought we were done, but came across one last problem. When we did the IN/out reset above, the macro for hiding all toolbars was in conflict and had to be disabled. Have tried some other ways of getting that to work by using the modules field, however, nothing has worked so far. Is their a way for this workbook to open with NO toolbars but with scrollbars on, but not full screen if possible, then have it revert back when closing the workbook, and not effecting the other macro's. Have looked all over the internet and have not come up with a workable solution. Thx again, am attaching the current file.

 

[SkipVought]

In conflict. What does that mean?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Benaway]

The original was place into the Microsoft excel objects in the "ThisWorkbook". Was enter as a Private Sub Workbook_Open(), which is where you place the Resetinout micro. Stated I could only have one Workbook Open() micro. Was not sure how to enter it a different way.

 

[SkipVought]

Simple. Call your macro either before or after the ResetInOut macro in the Workbook_Open event.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]