Timer Application in Excel

Bright01: This can easily achieved using the Timer API.

Here is an example on how to do it. You will have to amend it for realistic situations :)

In Workbook Open Event

Private Sub Workbook_Open()
    StartTimer
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    EndTimer
End Sub

Select allOpen in new window

In Module

Public Declare Function SetTimer Lib "user32" ( _
ByVal HWnd As Long, ByVal nIDEvent As Long, _
ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long

Public Declare Function KillTimer Lib "user32" ( _
ByVal HWnd As Long, ByVal nIDEvent As Long) As Long

Public TimerID As Long, TimerSeconds As Single

'~~> Start Timer
Sub StartTimer()
    '~~ Set the timer.
    TimerSeconds = 1
    TimerID = SetTimer(0&, 0&, TimerSeconds * 1000&, AddressOf TimerProc)
End Sub

'~~> End Timer
Sub EndTimer()
    On Error Resume Next
    KillTimer 0&, TimerID
End Sub

Sub TimerProc(ByVal HWnd As Long, ByVal uMsg As Long, _
ByVal nIDEvent As Long, ByVal dwTimer As Long)
    '~~> CODE HERE TO CHECK FOR DATE CHANCE AND DO NECESSARY STUFF
End Sub

Select allOpen in new window

Sid

Sid,

Much appreciate it.  However, by now you must have recognized that I'm a novice user and closet wantabe tech. guy....... so I'm not that good.  Please look at the workbook and you will see it gives you 3 options; I need a 4th..... to lock the sheet past a certain date.  I currently have this sheet as a separate sheet that checks the date when opened to compare it and then take the action that has been choosen.  With the 4th option, it will lock the sheet until a proper password is entered.  

Can you send me back the modified workbook?

Thank you,

B.

Sid,

I think the approach you have recommended may not take into account the sheet/macros I already have.  Can you clarify?

Thank you,

B.

Password?

Sid

Sorry!  "password"

Ok, I opened the sheet and got an error. That is because of your workbook open event.

Private Sub Workbook_Open()
Worksheets("Sheet1").RunCheck
End Sub

Select allOpen in new window

There is no Sheet1?

Now could you run me through exact steps that you want to achieve in this workbook?

Sid

Sid,

Here is a better version that describes what I'm trying to accomplish.  "In a nutshell"..... If the time has been exceeded on start-up of the workbook, the macro needs to execute one of four or a multiple of any of the 4 actions.  1.) delete a range within a sheet, 2.) delete a sheet, 3.) lock a sheet, 4.) lock and notify the Workbook.  I'm also assuming the sheets that may be locked, may be hidden.

That's it !!

File attached...and thank you in advance for your patience...... I've been on the road alot lately.

B.
timer5-application.xls

Sid,

Only the 4th option isn't working; First 3 now work.  Can you take a look?

B.

Bright01: There are few things which seems incorrect to me.

1) The workbook open code is incorrect

Private Sub Workbook_Open()
    Worksheets("Sheet1").RunCheck
End Sub

Did you mean to put it as

Private Sub Workbook_Open()
    Worksheets("Timer").RunCheck
End Sub

2) There are references in the code which refer to cells which have no data? Especially time comparisons? Is this your actual workbook?

3) >>lock and notify the Workbook.  I'm also assuming the sheets that may be locked, may be hidden.

Could you explain what do you mean by locking and notifying the workbook? Open in read only mode?

Sid

Hi Sid,

1.) Yes.... I should have it as you have suggested "Timer".
2.) Yes ... this is the actual workbook; but it has been modified over time.  IF you could have a look and see what needs to be added or changed that would be a great help.  I think you understand what I'm trying to do with the 4 scenarios - Delete range, Delete Sheet, Lock Sheet and Lock Workbook.
3.) In the workbook there are sheets that are open, sheets that are protected and sheets that are hidden (it's a big, complex workbook).  I'd like to be able to have this code apply to any sheet with any condition.
4.) The forth condition, Lock Workbook, would, at a certain point in time, force the user to call back in order to get a passcode to unlock the Workbook.  It could be as simple as Open in Read Only since the Workbook requires input in order to do its calculations and if read-only, then the Workbook is useless unless they call.  So that would work.

Hope that helps.  Appreciate your work.

B.

Do you want me to recreate it?

Sid

I'd never ask you to do that.  However, if it's a short 5 to 10 min. exercise for you, and that's what you prefer rather then modify or trouble shoot what I already have...... I certainly wouldn't complain.  And given the work you've done previously, it's liable to be 2X to 5X better.

Your call,

B.

Check back in 15 mins :)

Sid

I have just created a basic sheet. The timer is not working at the moment (I am still working on it)

See, if this format is ok? We will take it form there?

Sid
timer5-application.xlsm

Sorry use this

Sid
timer5-application.xlsm

Sid,

This looks great!  Two questions;  1.) If I have to use the name of the sheet (assuming it's named/labeled), do I reference the sheet in the app. as Sheet# or "Name of Sheet" or Name of Sheet?  And 2.) what about the 4th case where the workbook needs to be "read-only" ?

B.

>>1.) If I have to use the name of the sheet (assuming it's named/labeled), do I reference the sheet in the app. as Sheet# or "Name of Sheet" or Name of Sheet?

Name of Sheet

>>2.) what about the 4th case where the workbook needs to be "read-only" ?

I am working on it. Just wanted you to first approve the format

Also when you say, delete a range, do you mean actually delete the range and shift cells up or simply clear the contents?

Sid

Clear the Contents.  No need to shift.

Thx.

B.

Sorry had stepped out. everything is done. Now only the last part is left.

>>>I need the ability to run a small macro that locks either the spreadsheet or the workbook so that after the date passes, you must enter a new password. I would like this macro also to force a user form that directs the user on where to go to get the password.

Few questions

1) locking the worksheet. Do you want to protect all the worksheets in the workbook with a new password?

2) Where do you plan to store the password?

Sid

Sid,

Good morning!  If you're ever in the Carolinas, please look me up!  I owe you dinner.

I'd like to lock the entire Workbook and with possible, notification (perhaps using a standard user form) with a input box to put in the password that unlocks the Workbook. I would assume you either store this password in "This Workbook", the Timer worksheet or a new module.  I'm not sure where the best place is.  The intent is to force a user to call me to get the password to unlock the workbook.  

Does that make sense?

B.

>>> The intent is to force a user to call me to get the password to unlock the workbook.  

How would you know what the new password is if the password changes realtime at user's end?

Sid

Could you have a field in the Timer App. that allows me to predetermine the password and that way when the workbook locks, they would have to use the new password that's in the Timer to unlock the Workbook?

B.

Yes but my question is that if the file is with the user and let's say the user if 1500 miles away from you then how will you know what is the password in that file?

Sid

Actually could happen!  I'm not clear.  I'd get a call and would tell them to put in "the password" which would be the same password (not random).  I would be deciding if this was a ligitmate user or not.  But the password would be the same for each copy.  It's not a volume app.; probably 100 users max.

Does that make sense?

B.

So we would set a "default" password in the Timer Sheet that would become the new Password when time had expired and the workbook was locked.  Seems the simpliest approach.

B.

Sid,

If this is too difficult, just send me what we have on the first three functions and I'll work around the forth.

Hope all is well.

B.

>>>So we would set a "default" password in the Timer Sheet that would become the new Password when time had expired and the workbook was locked.  Seems the simpliest approach.

Sorry was kinda busy.

But then the password will be the same every time :) And that defeats your purpose. What we can do is pregenerate 1000 passwords in a column and everytime it picks a new password and clears the old one? Sounds ok?

Sid

I also realized that it may not be the safe way of doing it. as one can easily get that password.

Sid

Sid,

Thanks for responding.  And believe me.... I understand "being busy"!  Things are kind of crazy.  So we could pregenerate passwords; however, I think that adds a new level of complexity.  How can I give them a new password when it may be 1 of 1000 pregenerated?  I'm thinking that the "stored" password forces a single call.  What I would do is force a new copy to be downloaded.  So they don't actually unlock the existing spreadsheet.  So what I'm looking for is a timer (in the 4th option), to take an assigned password, and lock the workbook.  This forces a call (hopefully with a splach screen to say call XXX at XXX). Then what happens is they get a new copy. That way they have never seen the hidden password.

Does that work?

B.

B

This is the file till now. The password is till an issue. I am not able to formulate a logic in creating a password so that when the user calls you, you can give them a password.

>>>So what I'm looking for is a timer (in the 4th option), to take an assigned password, and lock the workbook.  This forces a call (hopefully with a splach screen to say call XXX at XXX). Then what happens is they get a new copy. That way they have never seen the hidden password.

Yes this can be done. Let me try and incorporate that.

Sid
timer5-application.xlsm

B

Ok here is the updated file.

Right now, I haven't changed the "Open Workbook Password". It is what it was. The sheets at the moment are protected with password "PASSWORD". Once the due date is met, the password of all the sheets will change to "LOCKME". You can set the passwords in Module1.

Also we wouldn't require the timer facility. The sheets will automatically get locked

1) When the workbook opens OR
2) There is any 'Change' in Sheet "DONOTDELETE" AND
3) When the user tries to save the workbook after due date. The Sheets will be locked and the changes that are made after the due date will be discarded.

So I guess we do not need a timer. When the worksheets get locked, it will display a message with the contact number. You may amend the contact number and the message in sub lockme() in module1.

Sid
timer5-application.xlsm

Sid,

Greetings.  I tried to save and launch the app. and I get an error on the "IF NOW" Statement.  Additionally, something now runs on the system that is odd;

"DUMMY LAYERED FLICKER HIDER" whatever that is.  A virus?

Sub lockme()
    OldGlobalPassword = "PASSWORD"
    NewGlobalPassword = "LOCKME"
    If Now > Sheets("DONOTDELETE").Range("B1").Value Then
        On Error Resume Next
            For i = 1 To ThisWorkbook.Sheets.Count
                If Sheets(i).Name <> "DONOTDELETE" Then
                    Sheets(i).Unprotect OldGlobalPassword
                    Sheets(i).Protect NewGlobalPassword
                    MsgBox "The worksheet is this workbook has been locked. Please contact B on XXX-XXX-XXX-XXX for a new workbook"
                End If
            Next i
        On Error GoTo 0
    End If
End Sub

>>> "DUMMY LAYERED FLICKER HIDER" whatever that is.  A virus?

I am not sure. What exactly happens?

What error are you getting in the IF NOW statement?

Sid

Sid,

I saved the file and rebooted and it works fine now.  Question to you on usability; I see that if I hit the buttons, the macro goes off.  I'm assuming the macros also go off it the date is checked and now>the set date?  Also, the new "global password"; Is that the new password the password in the file..... "LOCKME"?  Which I can change...... in the code.........  What happens to all the other passwords in the workbook?  Is the Global Password separate?  So when I unlock it with LOCKME, the other passwords are still in place or does it replace all passwords?  

This looks very good... I just need a little help with how it works.

Thank you,

B.  

Sid,
Sorry for the delay; I've had to try to make this work in my production system.  What I have learned is what I need is for this macro to "lock the WORKBOOK" instead of a worksheet.  I'm going to ask a related question to enhance the macro.

Much Thanks,

B.