1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Cell Protection (MS Excel 97)

Discussion in 'Business Applications' started by 123helpplz, Nov 11, 2004.

Thread Status:
Not open for further replies.
Advertisement
  1. 123helpplz

    123helpplz Thread Starter

    Joined:
    May 31, 2003
    Messages:
    23
    Hi, I have a document that I wish to protect specific cells in. I have done this and whenever I double click or attempt to type in a protected field an error message appears. This is a good thing.

    What I want to know is whether or not it is possible to prevent single clicking on protected cells as well. Basically, for the few fields I want data to be allowed to be entered in, I want them to be the only accessible fields. I don't even want to be able to do as little as click on cells other than the few left unprotected.

    Is it possible to prevent any form of access, highlighting, clicking, typing on protected cells?
     
  2. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
  3. 123helpplz

    123helpplz Thread Starter

    Joined:
    May 31, 2003
    Messages:
    23
    The first part of that worked for me perfectly. When I entered the VBA code and opened the document, a run time error occurred.

    Run-time error '9':

    Subscript out of range

    I went to Debug and this is what it said

    Private Sub Workbook_Open()
    Worksheets("Sheet1").EnableSelection = xlUnlockedCells
    End Sub

    Highlighted in yellow was the middle line:

    Worksheets("Sheet1").EnableSelection = xlUnlockedCells

    How is this fixed?
     
  4. kiwiguy

    kiwiguy

    Joined:
    Aug 17, 2003
    Messages:
    17,584
    Does your workbook have a "Sheet1"? or have you renamed it? If so rename it in the script as well.
     
  5. 123helpplz

    123helpplz Thread Starter

    Joined:
    May 31, 2003
    Messages:
    23
    I'll try that. I'm new with this script stuff. I have 3 seperate sheets, how would I apply this to all of them?

    Instead of it being just "Sheet1", would it be "Sheet1", "Sheet2", "Sheet3" or would it be a separate entry?
     
  6. 123helpplz

    123helpplz Thread Starter

    Joined:
    May 31, 2003
    Messages:
    23
    This did the trick:

    Private Sub Workbook_Open()
    Worksheets("Sheet1").EnableSelection = xlUnlockedCells
    End Sub
    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Worksheets("Sheet2").EnableSelection = xlUnlockedCells
    End Sub
    Private Sub Workbook_Activate()
    Worksheets("Sheet3").EnableSelection = xlUnlockedCells
    End Sub

    Now all three sheets in this workbook are protected other than the cells that are accessible. Thanks for the help!
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/295231

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice