Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Aug 2001
    Location
    Bloomington, Indiana, USA
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Protection and sub-totals (2007)

    Good morning,

    I have a financial worksheet that gets appended monthly with budgets and actual income and expense from a data warehouse. I have a macro that does a lot of work to the raw data and formats and sub-totals the data into a readable format, there are actually 6 levels of subtotaling. My problem now is the managers that review this data want to be able to add information about specific transactions in a “comment” column and have this information carry forward with the detail line as the book gets appended.
    Because of the way the data is appended, I have to remove all of the sub-totals, add the new months data and then re-apply the sorts and sub-totals. In order to retain their comment information I have to force the managers to put their comments only on the detail line or it will get lost when we update the spreadsheet at the end of the month. I thought I could unlock only the rows that do not have sub-totals and then protect the worksheet and everything would work fine. I did a trial of unlocking just the detail rows and then protecting the workbook, but now the user cannot change the outline level.
    Does anyone have any suggestions that might work to resolve this situation?
    Greg <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  2. #2
    Star Lounger
    Join Date
    Jun 2001
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protection and sub-totals (2007)

    Is this what you meant?

    <pre>Private Sub Workbook_Open()
    With Worksheets("Sheet1")
    .Unprotect Password:="password"
    .EnableOutlining = True
    .Protect UserInterfaceOnly:=True, Password:="password"
    End With
    End Sub</pre>


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

    Re: Protection and sub-totals (2007)

    Perhaps you could use a pivot table instead of subtotals. You can allow the use of (existing) pivot tables in a protected sheet.

    Added later: see the next reply for a better suggestion. I had forgotten about that one! <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

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

    Re: Protection and sub-totals (2007)

    Much better, thanks!

  5. #5
    Star Lounger
    Join Date
    Jun 2001
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protection and sub-totals (2007)

    I take so much from this site...glad to be able to contribute something
    <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

  6. #6
    Star Lounger
    Join Date
    Aug 2001
    Location
    Bloomington, Indiana, USA
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protection and sub-totals (2007)

    Sorry for the delayed response but I got pulled to another project for a few days and I'm just now getting back to this one.

    Your code does most of what I need, but now I am finding that I need to test to see if protection is enabled before I apply the code. Here is what I am using:

    Dim wshtarget as Worksheet
    Set wshTarget= Worksheets ("View")
    wshTarget.Activate
    If ActiveSheet.Protection= True Then
    with Worksheets("View")
    .Unprotect, Password:="password"
    End With
    End If


    When I run this piece of code, I receive a Run-Time error '438':
    Object doesn't support this property or method.

    I have looked at the help files and can't see where I am going wrong. The method I am using came from a Ozgrid Post
    http://www.ozgrid.com/forum/showthread.php?t=67806

    Any help would be approciated!
    Greg <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

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

    Re: Protection and sub-totals (2007)

    Protection is not a True/False property but an object. You can use the ProtectContents property instead:

    If ActiveSheet.ProtectContents Then

  8. #8
    Star Lounger
    Join Date
    Aug 2001
    Location
    Bloomington, Indiana, USA
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protection and sub-totals (2007)

    Thanks! That's what I get for straying from the Lounge!!!
    Greg <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

Posting Permissions

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