Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    protect Excel formulas

    How do I prevent other users from entering anything at all into certain cells that contain formulas?

  2. #2
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    eastern Connecticut, Connecticut, USA
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: protect Excel formulas

    Select your formula cells - Format/Cells/Protection and check the Locked option - Then to activate it, you have to protect the worksheet - Tools / Protection / Protect Sheet. Unfortunately, this brings with it many restrictions like not being able to apply formatting on the sheet etc.

  3. #3
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: protect Excel formulas

    the thing that strikes me as perhaps the most annoying and least expected about turning on protection is the file bloat (i've seen up to 50% increases).

    Brooke

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: protect Excel formulas

    I don't know if this can be of any help, but to prevent users to enter or change something in specific cells - instead of unlocking cells and protecting the sheet - you can use the worksheet_change event, like this (Suppose there is a formula in cell "C5", then you can prevent someone of changing its contents by putting the following code in the worksheet_change event:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = Range("C5") Then
    MsgBox "Sorry you are not allowed to edit this cell"
    Application.EnableEvents = False
    Application.Undo
    Application.EnableEvents = True
    End If
    End Sub

    You need to disable the application-events, by putting application.enableevents = false before you do the application.undo otherwise you will end up in a continuous loop of change-events. Once the last change has been undone, you need to reset the application-events to true.

  5. #5
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: protect Excel formulas

    Would this method be affected by the user disabling macros?

    Brooke

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: protect Excel formulas

    Thank you sooooooo much - I'll try it. In the meantime, I advised the person who wanted my advice to select those cells she didn't want anyone to be able to change and had her apply a data validation to them whereby she specified an allowable entry to be something no one would ever want to enter with a warning not to type in that cell.

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: protect Excel formulas

    Yes, you need to enable macros, otherwise it will not work.
    What you can do is protect your sheet before save (this can be done by protecting the sheet in the workbook_beforeclose event, so that it cannot be changed if the macros are disabled during opening. The first thing you do after enabling the macros is to fire a small macro to unprotect the sheet. This can be done in the workbook_open event of thisworkbook. I hope this does not sound to chaotic, I am just writing it down by heart. Anyway, give it a try, you will enjoy all these events Excel is providing you.

    Simply add
    ActiveSheet.UnProtect to the workbook_open event (of course be sure that the activesheet is the one with the change-event macros)

  8. #8
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: protect Excel formulas

    Just forgot to mention that I am using these events mainly for validation purposes. It is clear that you can write your own validation rules in this worksheet_change event procedure. e.g. if the value in the cell equal to Target is not between two predefined values, you fire your own warning message and empty the cell with a simple e.g. Range("C5").clearcontents.

Posting Permissions

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