?
Solved

Lock cells in excel spreadsheet

Posted on 2011-10-10
6
Medium Priority
?
412 Views
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.
0
Comment
Question by:gregfthompson
  • 3
  • 2
6 Comments
 

Author Comment

by:gregfthompson
ID: 36946116
3193-Revenue---Segments---Chiro.xls

Here's the spreadsheet file.
0
 
LVL 50
ID: 36946243
Hello,

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
    Me.Unprotect
    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
        Else
            Me.Cells(16, 8) = ""
            Exit For
        End If
    Next
    Me.Protect
    
End Sub

Open in new window


cheers, teylyn
0
 

Author Comment

by:gregfthompson
ID: 36946350
Thanks Teylyn,
Can you send me the file with this working?
Thanks again,

Greg
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50
ID: 36946421
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
0
 

Author Comment

by:gregfthompson
ID: 36971756
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

Thanks,

Greg
3193-Revenue---Segments---Chiro-.xls
0
 
LVL 2

Accepted Solution

by:
bearblack earned 2000 total points
ID: 37662998
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".
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

809 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