Page 1 of 3 123 LastLast
Results 1 to 15 of 38
  1. #1
    Star Lounger
    Join Date
    May 2007
    Location
    California, USA
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    changes to saved workbooks (2003 Service pak2)

    Hello All,
    I have a question that hopefully someone can offer a solution. I have a workbook with a sheet for each day of the month to record all sales and balance the previous day

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: changes to saved workbooks (2003 Service pak2)

    You could use the Workbook_BeforeSave event to protect specific worksheets. We'd have to know how the worksheets are named to give more detailed help.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: changes to saved workbooks (2003 Service pak2)

    After completing the changes to a sheet, you could do a Tools/Protection/Protect Sheet. You could also do this automatically by using the Worksheet Change event routine to record which sheets have been changed and the Workbook Before Save event routine to protect all sheets that were changed since the last save.
    Legare Coleman

  4. #4
    Star Lounger
    Join Date
    May 2007
    Location
    California, USA
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: changes to saved workbooks (2003 Service pak2)

    Hi Hans and Legare,
    Thanks for the responses. What I have is a workbook containing 55 sheets. 28 to 31 of the total sheets are for recording sales figures for each day of the month. The remaining 24 sheets are various data collection and recap sheets that compile data as the month progresses. Sales bonuses, commissions and deductions are paid on the month end sales figures. So, after a days figures are entered it

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: changes to saved workbooks (2003 Service pak2)

    See my previous reply.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: changes to saved workbooks (2003 Service pak2)

    You can set the EnableSelection property of a protected worksheet to xlNoSelection. The user won't be able to select any cell, even if it is unlocked, so editing will be impossible.

  7. #7
    Star Lounger
    Join Date
    May 2007
    Location
    California, USA
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: changes to saved workbooks (2003 Service pak2)

    Hi Hans,
    I have been unable to locate any information on "workbook-_before save". Attached is the worksheet for 1st day of month, file size prevents sending any of the other sheets.

    The data entry is made in the yellow cells which are unprotected. There are hidden sheets that collect and compile data through out the month. The hidden sheets are named on sheet tabs according to what data is being collected.

    Example of problem: on 1st sheet cash collected shows serious shortage, near end of month someone changes the cash amount to a ficticious number. Now the information on all data collection sheets would be inaccurate.

    The workbook is available to employees for shift check purposes all month long and used twice a day. After each use the the workbook is saved.

    Thanks for any help.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: changes to saved workbooks (2003 Service pak2)

    Are the sheets for the days all named like that: 1st, 2nd, 3rd, ... 31st?
    If so, are there any other sheets in the workbook whose name begins with a digit?

  9. #9
    Star Lounger
    Join Date
    May 2007
    Location
    California, USA
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: changes to saved workbooks (2003 Service pak2)

    Yes, daily sheets numbered 1 trough 31. No other sheets use numbers for names.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: changes to saved workbooks (2003 Service pak2)

    The worksheet wasn't protected. I assume that was a mistake. If not, please let me know.

    Press Alt+F11 to activate the Visual Basic Editor.
    Locate your workbook in the Project Explorer on the left hand side.
    Double click the ThisWorkbook node under Microsoft Excel Objects.
    Enter or paste the following code into the module that appears:

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim wsh As Worksheet
    Dim intC As Integer
    Dim dtmDate As Date
    For Each wsh In Me.Worksheets
    intC = Val(wsh.Name)
    If intC > 0 And intC < Day(Date) Then
    wsh.EnableSelection = xlNoSelection
    End If
    Next wsh
    End Sub

    Switch back to Excel.
    When you save, selecting cells will be disabled in all worksheets for days to today.

  11. #11
    Star Lounger
    Join Date
    May 2007
    Location
    California, USA
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: changes to saved workbooks (2003 Service pak2)

    Hi Hans,
    The sheet I sent was unprotected for your use, it would normally be protected.
    I followed the instructions but cannot get it to work. The protection on workbook and sheets is on.

    Should I click save in Visual Basic Editor after entering the code. I tried save and close on VBE with no change in Excel.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: changes to saved workbooks (2003 Service pak2)

    Please don't post basically the same reply twice.

    The sheets can remain protected before, during and after entering the code.
    There is no need to "save Basic editor" before shifting back to Excel. It's able to fend for itself.
    The code will (or should) only run when you save the workbook.

    Make sure that you entered the code in the ThisWorkbook module of your workbook, not in one of the sheet modules, nor in a standard module.

  13. #13
    Star Lounger
    Join Date
    May 2007
    Location
    California, USA
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: changes to saved workbooks (2003 Service pak2)

    Hi Hans,
    Thanks for the help. I left that sheet sent you unprotected intentionally.
    I entered the code as you instructed but could could not get it to work. Do I need to have the workbook and sheets unprotected prior to entering the code?
    After entering the code should I close and save Basic editor prior to shifting back to Excel?

    The following line is in red font in VBE. If intC > 0 And intC < Day(Date) Then

  14. #14
    Star Lounger
    Join Date
    May 2007
    Location
    California, USA
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: changes to saved workbooks (2003 Service pak2)

    Hans, Sorry about the double post. Not yet real comfortable with posting, missed the "All" and thought the post had not been made <img src=/S/bash.gif border=0 alt=bash width=35 height=39>

  15. #15
    Star Lounger
    Join Date
    May 2007
    Location
    California, USA
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: changes to saved workbooks (2003 Service pak2)

    Hi Hans, I may have goofed again. The line below was added to a previous post by editing. I suspect you may have missed it.

    The following line is in red font in VBE: If intC > 0 And intC < Day(Date) Then

    Thanks for your patience <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

Page 1 of 3 123 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
  •