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

ilrosebud26Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
TRobinJamesConnect With a Mentor Commented:
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 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
TRobinJamesCommented:
Actually, sorry xlLockedCells is of course valid, however, I am not sure you want to use it in this case.
0
 
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
 
ilrosebud26Author Commented:
I was also out of the office.  Thanks for your help.
0
All Courses

From novice to tech pro — start learning today.