Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 712
  • Last Modified:

Disable access to lock cells in a worksheet

I have disabled access to certain cells in a spreadsheet through a macro but it appears that the option to select the locked cells is still on.  What do I do to correct this?  I have attached my macro for reference.
If FormatCustomer Then
               Workbooks.Open Filename:="C:\my documents\Customers.xls"
               'Format IDs as General
               Range("C:C,E:E,F:F").Select
               Selection.NumberFormat = "General"
               'Set font to nine pitch
               Cells.Select
                    With Selection.Font
                    .Name = "Tahoma"
                    .Size = 9
                    .Strikethrough = False
                    .Superscript = False
                    .Subscript = False
                    .OutlineFont = False
                    .Shadow = False
                    .Underline = xlUnderlineStyleNone
                    .ColorIndex = 1
                    End With
               'Set Columns to autofit
               Cells.Select
               Selection.Columns.AutoFit
               'Rename tab sheet1 to Customers
               Sheets("Sheet1").Select
               Sheets("Sheet1").Name = "Customers"
               'Define Name Billto
               rowa = Sheets("Customers").Cells(Rows.Count, 1).End(xlUp).Row
               ActiveWorkbook.Names.Add Name:="Billto", RefersToR1C1:="=Customers!R2C2:R" & rowa & "C2" & Chr(32)
               'Define Name BillToList
               rowb = Sheets("Customers").Cells(Rows.Count, 1).End(xlUp).Row
               ActiveWorkbook.Names.Add Name:="BillToLookup", RefersToR1C1:="=Customers!R2C1:R" & rowb & "C8" & Chr(32)
               'Define Name BillToLookup
               ActiveWorkbook.Names.Add Name:="BillToList", RefersToR1C1:="=OFFSET(Customers!R2C1,0,0,COUNTA(Customers!C1)-1)"
               'Protect Customers Worksheet
               ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="password"
               ActiveSheet.EnableSelection = xlLockedCells
               'Go to first cell on worksheet
               Range("A1").Select
               'Save with same name as opened
               ActiveWorkbook.Save
            End If

Open in new window

0
ilrosebud26
Asked:
ilrosebud26
  • 3
  • 3
1 Solution
 
ilrosebud26Author Commented:
I am assuming that reason I am have a problem is the when the workbook is created by default bot the select lock and unlocked cells is on.  I think I need to disable locked cells instead of enable unlocked cells.  I just cannot find the right syntac to complete the task.
0
 
TRobinJamesCommented:
Are you sure about this statement:

ActiveSheet.EnableSelection = xlLockedCells

Perhaps you want:

ActiveSheet.EnableSelection = xlUnlockedCells

so that users can select only unlocked cells.  I am not use xlLockedCells is a valid excel constant and may be defaulting to 0 which is  xlNoRestrictions
0
 
TRobinJamesCommented:
Actually, sorry xlLockedCells is of course valid, however, I am not sure you want to use it in this case.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
ilrosebud26Author Commented:
No, you are correct.  It should say xlUnlockedCells orxlNoRestrictions.  My problem is that even when it says either of these, the locked cells can be selected but not changed.  If I unselect the locked cell box through tools/protection the users cannot even select the cells that are locked.  I want to replicate this functionality.  Is that possible or this base functionality of the application?
0
 
TRobinJamesCommented:
Sorry, was out of office for a week.  If this is still relevant, the code below does not allow user to select or enter into cells, but allows selection of all cells outside range.
With ActiveSheet
    
    .Range("C9:E11").Locked = True
    .Protect
    .EnableSelection = xlUnlockedCells
End With

Open in new window

0
 
ilrosebud26Author Commented:
I was also out of the office.  Thanks for your help.
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.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now