Results 1 to 12 of 12
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Hidden Formulas (Office 97)

    Hi

    I have a spreadsheet with prices in Euros I use a formula to convert the cells to sterling to issue to salesmen. I then lock the cells and hide the formulas, protect the sheet so that they cannot read the rate. But of course if they copy and paste into another worksheet the formulas become readable again, is there any way to prevent this so they are never viewable. Apart from converting the formulas to values, which is slightly complicated because of merged cells.

    Thanks again
    If you are a fool at forty, you will always be a fool

  2. #2
    2 Star Lounger
    Join Date
    May 2002
    Location
    Midland, Michigan, USA
    Posts
    133
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hidden Formulas (Office 97)

    My thought is to create your own function, put it in a module, and protect the module (ensure you Lock the project for viewing) with a password. Even if the workbook is Saved As, the password is preserved - as far as I can tell, anyway.

    To adjust the rate, just edit it right in the function, since YOU will know the password.

    Have a look at the attached workbook - the password for the module is <font color=blue>test</font color=blue>.
    Attached Images Attached Images

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    Midland, Michigan, USA
    Posts
    133
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hidden Formulas (Office 97)

    Here's the workbook:
    Attached Files Attached Files

  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: Hidden Formulas (Office 97)

    You can create a hidden name, for example (I just put a number in I do not know the rate) with:

    ActiveWorkbook.Names.Add Name:="EuroToSterlingRate", RefersToR1C1:="=1.234", Visible:=False

    Then use the "rate name" in calculations.

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

    Re: Hidden Formulas (Office 97)

    If I recall correctly, if you also protect the workbook (and presumably all the worksheets in it), the -values- can be copied to another workbook, but not the formulas.
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Hidden Formulas (Office 97)

    Hi

    There's some interesting ideas here I wil give them a try

    Thanks to all who replied
    If you are a fool at forty, you will always be a fool

  7. #7
    Star Lounger
    Join Date
    Apr 2002
    Location
    Busselton, Western Australia, Australia
    Posts
    59
    Thanks
    1
    Thanked 2 Times in 2 Posts

    Re: Hidden Formulas (Office 97)

    The other way of course would be to create your final worksheet with all the calculations done and then Copy the data over to another worksheet using paste special values.
    This way there is only values and no possibility of anyone seeing or recovering your formulas, of course you have to distribute the later worksheet.
    If you record a macro as you do this you could automate the process of producing the distribution sheet quite easily.
    Cheers
    Brian.

  8. #8
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Hidden Formulas (Office 97)

    Hi braddy60,

    Regarding your statement:
    "I then lock the cells and hide the formulas, protect the sheet so that they cannot read the rate. But of course if they copy and paste into another worksheet the formulas become readable again".

    If you do "lock the cells and hide the formulas, protect the sheet" , then copying the cells that are locked & hidden on the protected sheet does not copy the formulae - only their formats and the values resulting from whatever calculations they had done can be copied and pasted elsewhere.Having said that, though, it would usually be a trivial exercise to reverse engineer a currency conversion.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Hidden Formulas (Office 97)

    Hi

    This sounds like the best idea for what I want.

    Thanks a lot
    If you are a fool at forty, you will always be a fool

  10. #10
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hidden Formulas (Office 97)

    Have you tried making one sheet "very hidden". The user can not access a "very hidden" sheet by trying to unhide it nor are they capable of printing the sheet. The code would be:

    Sheets("Sheet2").Visible = xlVeryHidden

    to make it visible again use: Sheets("Sheet2").Visible = true

  11. #11
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Hidden Formulas (Office 97)

    Hi All

    Thanks to all who responded, I have enough to be going on with.

    I'm grateful
    If you are a fool at forty, you will always be a fool

  12. #12
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Hidden Formulas (Office 97)

    Hi

    This looks interesting I will give it a try.

    Thanks very much.
    If you are a fool at forty, you will always be a fool

Posting Permissions

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