Solved

Timer Application in Excel

Posted on 2011-02-22
38
666 Views
Last Modified: 2013-11-25
I have this great little application / macro that was written by E-E Professionals.  I use it to insure that control can be maintained over spreadsheets that proliferate within an organization.  I need one little addition.  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.

Doable?

B. timer4-application.xls
0
Comment
Question by:Bright01
  • 18
  • 18
38 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34956634
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

Open 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

Open in new window


Sid
0
 

Author Comment

by:Bright01
ID: 34956838
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.
0
 

Author Comment

by:Bright01
ID: 34959735
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.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34967002
Password?

Sid
0
 

Author Comment

by:Bright01
ID: 34967220
Sorry!  "password"
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34979039
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

Open in new window


There is no Sheet1?

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

Sid
0
 

Author Comment

by:Bright01
ID: 35005644
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
0
 

Author Comment

by:Bright01
ID: 35015490
Sid,

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

B.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35017081
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
0
 

Author Comment

by:Bright01
ID: 35017382
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.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35017600
Do you want me to recreate it?

Sid
0
 

Author Comment

by:Bright01
ID: 35017781
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.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35017786
Check back in 15 mins :)

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35017908
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
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35017918
Sorry use this

Sid
timer5-application.xlsm
0
 

Author Comment

by:Bright01
ID: 35018011
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.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35018037
>>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
0
 

Author Comment

by:Bright01
ID: 35018120
Clear the Contents.  No need to shift.

Thx.

B.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35019951
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
0
 

Author Comment

by:Bright01
ID: 35025674
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.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35027895
>>> 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
0
 

Author Comment

by:Bright01
ID: 35030360

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.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35030429
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
0
 

Author Comment

by:Bright01
ID: 35031715
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.
0
 

Author Comment

by:Bright01
ID: 35036801
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.
0
 

Author Comment

by:Bright01
ID: 35059385
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.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35075274
>>>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
0
 
LVL 30

Expert Comment

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

Sid
0
 

Author Comment

by:Bright01
ID: 35088558
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.

0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35108282
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
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35108517
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
0
 

Author Comment

by:Bright01
ID: 35121324
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
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35121382
>>> "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
0
 

Author Comment

by:Bright01
ID: 35121662
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.  
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 500 total points
ID: 35130256
>>>I'm assuming the macros also go off it the date is checked and now>the set date?

Yes

>>>Also, the new "global password"; Is that the new password the password in the file..... "LOCKME"?

Yes

>>>What happens to all the other passwords in the workbook?

All passwords get replaced with "LOCKME" (the new password)

>>>So when I unlock it with LOCKME, the other passwords are still in place or does it replace all passwords?  

The file, when you open it has the password set as "PASSWORD". When the deadline is met all the passwords are changed to "LOCKME"

Hope this helps

Sid
0
 

Author Closing Comment

by:Bright01
ID: 35163725
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.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now