Lock cells in excel spreadsheet

Posted on 2011-10-10
Last Modified: 2012-05-12
I am attempting to lock all but 16 cells in a spreadsheet.  The process wroks but when an unlocked cell is clicked a window pops up saying "runtime error 1004".  It does not stop use of the unlocked cell but ti is annoying.
Question by:gregfthompson

    Author Comment


    Here's the spreadsheet file.
    LVL 50

    Expert Comment


    unprotect the sheet at the beginning of the code and protect it again at the end.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Dim FirstColNum As Long
        Dim LastColNum As Long
        Dim Total As Double
        Dim RowIndex As Long
        Dim cl As Range
        Total = 0
        For Each cl In Selection.Cells
            If cl.Row = 10 Then
                Total = Total + Me.Cells(12, cl.Column)
                Me.Cells(16, 8) = Total
            ElseIf cl.Row = 11 Then
                Total = Total + Me.Cells(13, cl.Column)
                Me.Cells(16, 8) = Total
            ElseIf cl.Row = (10 + 11) Then
                Total = Total + Me.Cells(12, cl.Column) + Me.Cells(13, cl.Column)
                Me.Cells(16, 8) = Total
                Me.Cells(16, 8) = ""
                Exit For
            End If
    End Sub

    Open in new window

    cheers, teylyn

    Author Comment

    Thanks Teylyn,
    Can you send me the file with this working?
    Thanks again,

    LVL 50

    Expert Comment

    Looking at this again.

    I cannot find any cells that are locked, to the protection won't really do much, anyway. The error also comes up when the sheet is not protected.

    The code errors out because in some cases the cells that you are trying to add contain text. For example if cl.column = 8 then Me.Cells(12, cl.Column) is H12, which is a merged cell that contains text.

    Stepping through the code, I can't really see what it is supposed to do. All the merged cells prevent proper selection of cells with code.

    Can you explain what this is meant to be doing? Most times the code just ends in the last Else statement Me.Cells(16, 8) = "", which should blank out H16. But in row 16 cells B through N are merged, so nothing really happens.

    What are you trying to achieve?

    cheers, teylyn

    Author Comment

    Hi Teylyn,

    I wanted to lock all cells except the cells with white background.

    I followed the Excel help instructions but kept coming up with the bug error whenever I clicked on any of the unlocked white background cells.

    The code for the attached is 1234


    LVL 2

    Accepted Solution

    You have to go to each cell you waat to have unlocked and format the cell by unchecking the locked under the protection tab.

    To activate the protection you will need to then protect the sheet making sure to check Select unlocked cells so the end user can change the values in each "White Background Cell".

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now