Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Elkins Park, Pennsylvania, USA
    Posts
    325
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Updating Links to a Protected Spreadsheet (Excel 2003)

    Hi, all. I hope some of you can help me figure out the best approach to this problem: I have a "public" spreadsheet--we'll call it UserSheet--that has formulae that are linked to a second spreadsheet called Payroll. The user should not be able to view or open Payroll, but still needs to update the linked data whenever UserSheet is opened.

    I figured UserSheet should probably be protected so that the user can't see the pathname of the PayRoll sheet in the formula bar, but protection still doesn't prevent them from seeing the Payroll pathname in the "Edit: Links..." dialog box. Then I thought I could just protect the Payroll sheet, so even if the user knows where it is stored, they couldn't open it without the password. The problem is, though, that they couldn't UPDATE it without knowing the password, either. And, unless I'm missing something, the .UpdateLink method doesn't have a Password argument.

    Am I looking at this from the wrong angle? Can someone look at this problem from a fresh perspective and hopefully help me out?
    <font face="Comic Sans MS">That's what you do in a herd; you look out for each other!</font face=comic> - Mike

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

    Re: Updating Links to a Protected Spreadsheet (Excel 2003)

    Instead of setting a password for the Payroll workbook, you could set the Visible property of all sheets (except for one info sheet) to xlSheetVeryHidden in the Visual Basic Editor, then set a password on the Visual Basic project. Users would still be able to open the Payroll workbook, but they'd have no way to make the relevant sheets visible.

  3. #3
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Elkins Park, Pennsylvania, USA
    Posts
    325
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating Links to a Protected Spreadsheet (Excel 2003)

    Brilliant! And way better than my (fleeting) idea of opening the Payroll sheet and immediately closing it (after the data was updated). I'm sure there'd be a quick-fingered user ready with a lightning-fast CTRL+Break at the ready...
    <font face="Comic Sans MS">That's what you do in a herd; you look out for each other!</font face=comic> - Mike

Posting Permissions

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