Results 1 to 10 of 10
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Shared workbook problems (XP Pro)

    I'm asking about this on behalf of a friend, so I may not have all of the details... sorry.

    From what he's shown me, he was a workbook that is shared across a LAN (shared for editing, I think). He wants users to be able to make changes to certain data (rows), but not others. To this end, I have suggested using styles with differing protection settings, to differentiate between what will and won't be modifiable once the sheet is protected. I don't know how well this idea will work though.

    As well as modify, he'd like users to be able to add and delete rows of the non-protected data variety. Once the sheet is protected, I don't think it's possible to insert rows. My thought was a custom menu/toolbar button, which would run appropriate VBA code to unprotect -> insert -> reprotect. This could also be used for the modify function. Are there any better alternatives here?

    He also wants to be able to apply autofiltering (which I know nothing about) to columns, and this method seems to be unavailable when the sheet is protected. Any suggestions here?

    thanks for any thoughts

    Alan

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

    Re: Shared workbook problems (XP Pro)

    1. I have never had much luck with shared workbooks.

    2. I think the question about different users having different permisssions came up recently, perhaps somebody who participated in that discussion will help.

    3. In Excel 2002, you can select which actions are still allowed in a protected sheet. Insert rows, delete rows and use AutoFilter are among the customizable settings in the 'Protect Sheet' dialog.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Shared workbook problems (XP Pro)

    Check out <post#=288868>post 288868</post#> for some ideas

    This was the post alluded to by Hans. The user wanted multiple passwords in excel.

    Steve

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Shared workbook problems (XP Pro)

    Concerning autofilter and protection. You can autofilter with protection on (in other XL versions):

    Turn on autofilter - (data - filter -autofilter)
    Turn on Protection (tools - protection - protect sheet) add a password if desired

    Open VB (alt-f11)
    Open "project explorer" (Ctrl-R)

    In the "explorer window" dbl-click on the object for the protected sheet with the autofilter

    In the macrocode pane (usually on the right) add this code:

    <pre>Private Sub Worksheet_Activate()
    ActiveSheet.EnableAutoFilter = True
    ActiveSheet.Protect contents:=True, userInterfaceOnly:=True
    End Sub</pre>


    Close and return to excel (Alt-Q)

    Now you can autofilter with protection.

    Steve

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Shared workbook problems (XP Pro)

    Thanks Hans. I don't think there's a problem with differing security levels amongst users - they would all have the same restrictions, from what I can gather. I'll certainly follow up the customizable settings in the 'Protect Sheet' dialog. I only have XL 2000 at home, so can't play with these myself. This might be the answer he is after.

    Alan

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Shared workbook problems (XP Pro)

    I would ike to stress the first point Hans made in his first reply in this thread.

    Excel isn't at all reliable when it comes to sharing workbooks.

    My advice : STAY AWAY FROM SHARED WORKBOOKS (and I am deliberately shouting here)

    Try to convince your friend now is the time to start using Access (sounds as if the thing is still under development).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Shared workbook problems (XP Pro)

    OK, thanks Jan. I think he's already been bitten by some of the shared workbook bugs. I'll pass on the confirmation of this from the experts. I have no personal experience with them to pass on, but I gather he's expecting things to run like a shared database with "scheduled transaction" capabilities, which I'm sure is far from the reality.

    I think he's even more clueless with Access than I am, so maybe he's loathe to change. But being a fairly simple project, I might be able to convince him to use it as the starting block to a new learning experience.

    Alan

  8. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Shared workbook problems (XP Pro)

    Thank you Steve. I'll certainly give your autofiltering idea a go - this is probably his biggest bugbear I gather. Thanks also for the post reference, although different passwords and privilege levels are not an issue I gather.

    (Edited - more information)

    I had a few problems using the Worksheet_Activate() sub you suggested, but had good success using the general method therein. The "userInterfaceOnly" parameter certainly improves the flexibility of the whole protection scenario. I ended up adding a toggle button to the SS itself, and it seems to work as required - both enabling/ disabling autofiltering and showing/ hiding the dropdown arrowheads.

    Handy code. Thank you.

    Alan

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Shared workbook problems (XP Pro)

    The worksheet activate sub goes in the worksheet object of the sheet, not a module. Then it will run whenever the sheet is activated.

    Steve

  10. #10
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Shared workbook problems (XP Pro)

    Yep, that's where I had it and it did run without problems. It's just that I/he needed a toggle for the autofiltering and I also had some problems with the dropdown arrows not showing up, until the correct "region" of the cells were clicked, then they'd appear one by one. I suspect it may be a display refresh - type problem. Anyway, works well now.

    Alan

Posting Permissions

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