INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Macro to reset dropdown at a given system time

Macro to reset dropdown at a given system time

(OP)
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.

RE: Macro to reset dropdown at a given system time

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Macro to reset dropdown at a given system time

(OP)
That is correct, that would work perfect for us.....

RE: Macro to reset dropdown at a given system time

In Sheet2, enter

PrevDate

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

CODE

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Macro to reset dropdown at a given system time

(OP)
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.

RE: Macro to reset dropdown at a given system time

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Macro to reset dropdown at a given system time

(OP)
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....

RE: Macro to reset dropdown at a given system time

(OP)
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.

RE: Macro to reset dropdown at a given system time

In conflict. What does that mean?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Macro to reset dropdown at a given system time

(OP)
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.

RE: Macro to reset dropdown at a given system time

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close