Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    Jun 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    protections without protection (97)

    I want to have the effect of protecting worksheets -- specifically, preventing users from over-writing formulas -- without actually enabling worksheet protection.

    The reason that I don't want to use worksheet protection is that I want users to be able to sort data and use the filter arrows. In Excel 97, worksheet protection prevents this.

    Does anyone know of any functionality (probably using VBA) that can prevent users from overwriting formulas. For example, can I use VBA to restrict users to unprotected cells or restrict them to cells within certain range names?

    thanks in advance

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: protections without protection (97)

    If the range you want to protect is B2:E4, then the following code, places in the worksheet change event routine, will prevent the user from entering anything into that range.

    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, ActiveSheet.Range("B2:E4")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    MsgBox "You can't alter this cell.", vbExclamation + vbOKOnly
    Application.Undo
    Application.EnableEvents = True
    End Sub
    </pre>

    Legare Coleman

  3. #3
    New Lounger
    Join Date
    Jun 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: protections without protection (97)

    Thanks for your help.
    Where do I find "the worksheet change event routine"?

    I tried pasting the code into a new module & I tried looking in the worksheet 'properties' dialoge box and neither worked. The help file has no reference to 'change event routine'.

  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: protections without protection (97)

    In the project explorer, usually the upper left hand pane of the VBE window set, double click on the ThisWorkbook object under the project that is the Workbook you want to work on.

    At at the top of the right side code window which will then open, there are two drop down boxes; the left side drop down wil usually show "General"; click it & select "Workbook", then an empty Open event will probably be created for you (delete it if not wanted). Click the dropdown, you can scroll all the possible events, select "SheetChange".
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    New Lounger
    Join Date
    Jun 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: protections without protection (97)

    Thanks I got this to work.

    Any suggestions for how to use this with multiple sheet workbooks?

    I'm going to send out a multiple sheet workbook for users to enter data but the range that they will enter the data varies from sheet to sheet.
    As it works now, this function blocks out the same range in all sheets.
    I tried creating a range name and referencing the second parameter (ActiveSheet.Range("B2:E4")) with the range name instead. This didn't work however -- it worked fine for that sheet with the range name but I got errors when I tried to do anything on all other sheets. I'm assuming that this is because there was no 'intersection' of the range name on 'sheet1' with the current cell on sheets other than 'sheet1'.

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

    Re: protections without protection (97)

    Ugh! Unless a real VBA guru can help me (us), since Union doesn't appear to work across sheets, and arraying the sheets requires exactly the same cell range in each sheet, all I can come up with follows. Change the sheetnames and ranges to your actuals, add as many sheets as you want to the Select Case:

    <font color=red>Oops! See edit in red; additional line.</font color=red>

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    Dim rngR As Range
    Select Case Sh.Name
    Case "SheetName1"
    Set rngR = Sh.Range("A1:B20")
    Case "SheetName2"
    Set rngR = Sh.Range("A4:B10")
    Case "SheetName3"
    Set rngR = Sh.Range("B1:B40")
    Case "SheetName4"
    Set rngR = Sh.Range("A4:B40")
    End Select
    <font color=red> If rngR Is Nothing Then Exit Sub</font color=red>
    If Intersect(Target, rngR) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    MsgBox "You can't alter this cell.", vbExclamation + vbOKOnly
    Application.Undo
    Application.EnableEvents = True
    End Sub

    Let me know if this doesn't work.
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: protections without protection (97)

    My code was for the Worksheet Change event routine. John walked you through putting the code into the WorkBOOK change event routine where it would do the same thing for all worksheets. There is a Change Event routine for each sheet in addition to the Workbook Change event routine. To get to the Worksheet change event routine, right click on the sheet's name tab and select View Code in the pop-up menu. Then select "Worksheet" in the left drop down list and Change in the right box. You can put the code here for each sheet with a different range for each one.
    Legare Coleman

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

    Re: protections without protection (97)

    So I did. I get two demerits; one for not paying attention to the question and your exact code, and a second for reinventing the wheel.
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: protections without protection (97)

    Put on your sack cloth and stand in the corner. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Legare Coleman

  10. #10
    New Lounger
    Join Date
    Jun 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: protections without protection (97)

    Thanks for your help guys. This works great !

    One small change that I made (which you might want to keep in mind for future reference)... I used range names for the range parameter instead of cell addresses. This way I can select multiple, discontiguous ranges to be included in my no-change area. Also, range names will move as I insert/delete rows & columns.

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: protections without protection (97)

    The only problem with using range names is that the user can very easily defeat your protection by removing the cells he wants to change from the range.
    Legare Coleman

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

    Re: protections without protection (97)

    One more solution, without VBA.

    It will not prevent pasting data on top of the cells though.

    - Define a name (Insert, name, define) called "Locked", that refers to =1.
    - Select the cells you need protected
    - Data, Validation, Select Custom, enter this formula:
    =Locked<>1
    - select the Error alert tab and type a message (e.g. No Changes allowed to this cell).
    - OK

    Now to be able to edit the cells, set Locked to something else then =1.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  13. #13
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: protections without protection (97)

    Quite true, but since the post is using VBA anyway, I'd sugggest hiding the names and protecting the project so the user can't get to them (easily that is). <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>

    I always hide the named variables I use for my projects and use some meaningful prefix so it's easy to distinguish the names my code requires and those that a user may add for their own uses. I then hide them all at workbook open (and when the code adds new names itself).

    The names can be hidden from code like:

    Thisworkbook.Names("_MyRangeA").visible=false

    Deb <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

Posting Permissions

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