Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Iowa, USA
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    View protect individual sheets?? (Excel 2000/Win2K)

    Let's say I have a workbook with a number of worksheets. I want to send the same file to a number of people but want each person to be able to see ONLY their own sheet. Is this possible without resorting to VBA?

    I know you can password protect the sheets but that only keeps everyone from editing them...I don't want them to even SEE the data on the other sheets. Is it somehow possible to password the sheets for view access?

    Thanks!
    Sue

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: View protect individual sheets?? (Excel 2000/Win2K)

    Sue (without me setting up an example for testing, as I really don't have a testbed) set up the WB as follows:

    Require a password to open the Workbook (under Save, Options), AND lock the Workbook with a different password, AND lock all sheets each with the same password. Publish only the WB Open Password, use the other password(s) only in your VBA routines. Hide all sheets except a blank Sheet1. Lock the VBA modules from viewing.

    ... and now turn these steps into VBA:

    Use the Private Sub Workbook_Open() event to
    turn off screenupdating
    get the Application.Username
    use a Select Case to compare Usernames
    use Case to compare the openers' Username to each of your approved users' Usernames
    for each username
    unprotect the Workbook
    unprotect their sheet
    display their sheet
    turn on screenupdating
    use Case Else to issue an Unauthorized User Message and to close the Workbook if the username doesn't match an authorized user

    Because the code will be identical except for sheetname, the steps of unprotecting and unhiding each sheet should be in a subroutine called by the Select Case part of the Workbook_Open event and with the sheetname passed as a string parameter.

    Then use the Private Sub Workbook_BeforeClose(Cancel As Boolean) and Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) to protect and lock the sheet when the user is done.

    If you are not comfortable with VBA we can work on actual code for you.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: View protect individual sheets?? (Excel 2000/Win2K)

    I don't think theres is any easy way without VBA, but with or without VBA you are probably best off just sending the single appropriate sheet to each recipient. You could do that manually by coipying each sheet to a new workbook and sending it by File, SendTo, Mail Recipient etc. Or you can do it via VBA code as follows :<pre> Sub MailSheets()
    Dim sht As Worksheet
    For Each sht In ThisWorkbook.Sheets
    sht.Copy
    ActiveWorkbook.SendMail Recipients:=[A1].Text
    Next
    End Sub</pre>

    The above assumes that each sheet has the appropriate email address in A1. That can of course be changed to a different cell or some other approach can be adopted if that does not suit.

    This way you know each recipient is only seeing what you want them to see.

    Andrew C

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: View protect individual sheets?? (Excel 2000/Win2K)

    I'm having a more dense day than usual even for me. You asked if there was a way without VBA, and so what do I answer? Sorry. To completely avoid VBA, I can only think to suggest one-sheet workbooks with a separate password per user.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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