Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: Timer in Excel?

  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi!

    I'm using a spreadsheet at work and it is password protected. I was given the password to make changes if needed. I unlock it to make some changes and when done lock it again.

    Sometimes I have to prepare a report depending on the criteria so I unlock it, delete what doesn't apply, print the report, but when I go to undo the deletes it is password protected again and the undo list only has the last action on it. I have to close the spreadsheet without saving and open it again to continue.

    Can you have a timer on passwords or sheet protection of some sort?

    Just curious.
    Thanks,
    Louise

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    In theory, it's possible to activate a timer that will reprotect a worksheet or workbook, but I think it's more likely that there's an event procedure that does this, for example the BeforePrint event of the workbook.
    Look for the keyword Protect in the ThisWorkbook module of the workbook, and if you can't find anything relevant there, in the code modules of the individual worksheets.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Hans,

    Someone spent a lot of time on this spreadsheet. Nicely done too overall.

    There's 11 modules and I found one called Vprint which has:
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="1412" all over the place. Must be it. I guess the password wasn't too much of a secret since that is all over the place too.

    Do you think this is it?

    I was just curious, I don't want to mess anything up.

    Thanks again!
    Louise

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If Vprint is called when the workbook is printed, for example from the Workbook_BeforePrint event procedure in the ThisWorkbook module, that would explain why the workbook becomes protected again when it's printed.


  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    If Vprint is called when the workbook is printed, for example from the Workbook_BeforePrint event procedure in the ThisWorkbook module, that would explain why the workbook becomes protected again when it's printed.
    Ok, I'm looking in Visual Basic right? I click on "ThisWorkbook" under Microsoft Excel Objects, and look at the list below under "Properties - ThisWorkbook". I don't see anything listed named Workbook_BeforePrint or simply BeforePrint. Am I looking in the wrong place?
    Louise

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It is also possible that the print command is redirected to a macro. IN that case, there would be no before print event code. Alternatively, the programmer may also have used a class module to trap events.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    To view the code module for ThisWorkbook, double-click on the ThisWorkbook icon in the treeview on the left hand side of the Visual Basic Editor.


  8. #8
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    To view the code module for ThisWorkbook, double-click on the ThisWorkbook icon in the treeview on the left hand side of the Visual Basic Editor.
    Ok. The only thing that's there is a couple of commands to pgdn and then pgup on open and close. As Jan suggested, perhaps it's elsewhere.

    That's ok though, I'll just keep doing what I've been doing.

    Thanks!
    Louise

  9. #9
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by weese237 View Post
    ......I'll just keep doing what I've been doing.

    Thanks!
    You could post a copy of the workbook, with sensitive data removed? We could then pinpoint the issue and assist you in correcting it.

  10. #10
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by VegasNath View Post
    You could post a copy of the workbook, with sensitive data removed? We could then pinpoint the issue and assist you in correcting it.
    Ok, Here's a little stripped down template. The file is still quite large.

    Thank you
    Attached Files Attached Files
    Louise

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    In this version, VPrint does not have a line that protects a sheet.

    However, the worksheet module for the Checkbook sheet contains code that will unprotect and reprotect both the Data and Checkbook sheets each time the Checkbook sheet is activated.

    Most of the macros associated with the various command buttons on the sheets also unprotect and reprotect the sheet they're on.


  12. #12
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    In this version, VPrint does not have a line that protects a sheet.

    However, the worksheet module for the Checkbook sheet contains code that will unprotect and reprotect both the Data and Checkbook sheets each time the Checkbook sheet is activated.

    Most of the macros associated with the various command buttons on the sheets also unprotect and reprotect the sheet they're on.

    Thanks Hans. I figured there was something.
    Louise

  13. #13
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by weese237 View Post
    Sometimes I have to prepare a report depending on the criteria so I unlock it, delete what doesn't apply, print the report, but when I go to undo the deletes it is password protected again and the undo list only has the last action on it. I have to close the spreadsheet without saving and open it again to continue.
    I am unsure which worksheet you refer to as "print the report", however I assume that you are using one of the macro buttons to print? If so, then you will of course be unable to undo after doing so.

    However, you do not have any event procedures linked to printing, so you could use the print icon on the excel toolbar to print, instead of a macro print button, which should still allow you to undo afterwards.

    If you need more help, please let us know which worksheet you are referring to as "print the report", and also which macro(s) you are using to action the print.

    I hope this response is of some help.

  14. #14
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Nathan,

    I'm sorry I wasn't very clear. My bad. There's a couple of things going on here.

    1. Most of the time I work in the tab labeled "Month End" which is basically the same as Weeks 1-5. Our managers post all of their purchases/invoices there. Every week before I send the batch to corporate, I unprotect the "month end" tab and one of the weeks. I copy and paste all the information from month end to the week I'm working on. Then I delete the information from month end to get a clean sheet. I go thru all the invoices and mark the entries that I don't have an invoice for yet. (This means that these are un-received purchases.) Then I copy and paste those that I have checked back to month end. At this time, I often find that the month end tab has been re-protected and I have to unprotect again. ( I know this sounds like a lengthy procedure but sometimes these lists are quite long and I don't want to retype everything.) Then I click on the "hide" button and print preview/ print using the toolbar icon.

    2. What I have to do at month-end (which originally initiated this question ) is delete some of the amounts and corresponding distributions on the month end tab (if I've run out of tabs) because it's now the last week and month end. After "cleaning it up" to only show what I need, I click on "hide" which hides the empty rows, click on the print icon from the toolbar or print preview first and then print. Now I want to undo the deletes because I want to go the other way and show the accruals. I click on "Show All" and find that I can't undo because the list is gone and it's password protected once again.

    I hope you understand my explanation and I'm sorry if it's a little lengthy. Don't go crazy over this. I was just curious since I have never had a spreadsheet where I couldn't undo normally and where it would reprotect itself.

    Thank you!
    Louise

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The Hide Empty button on the Month End worksheet (and on the weekly sheets) runs this macro:

    Code:
    Sub ReceivingReportHide()
    '
    ' ReceivingReportHide Macro
    ' Macro recorded 2/11/2009 by mscott
    '
    
    '
    	ActiveSheet.Unprotect Password:="1412"
    	Selection.AutoFilter Field:=2, Criteria1:="1"
    	ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="1412"
    End Sub
    As you see, it protects the sheet again in the last line above End Sub. Also, running a macro will clear Excel's undo stack, i.e. you can't undo anything after running a macro.
    You could prevent the sheet from being reprotected by commenting out the line ActiveSheet.Protect ..., but clicking Hide Empty would still disable undo; that can't be avoided if you run a macro.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •