Locking specific cells

ERed1

Board Regular
Joined
Jun 26, 2019
Messages
104
Hi all,

I need some help fixing this code.

Code:
Worksheets("Sheet1").Range("A6:A167").Locked = FalseWorksheets("Sheet1").Range("C6:C167").Locked = False
ActiveSheet.Protect Password:="abc"

I am trying to lock those specific ranges, but it keeps throwing an error every time I try to implement it. The error reads "Unable to set the locked property of the Range class"

All help would be appreciated. Thank you in advance.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Try:
Code:
Sub test()
    With Sheets("Sheet1")
        .Unprotect Password:="abc"
        .Range("A6:A167").Locked = True
        .Range("C6:C167").Locked = True
        .Protect Password:="abc"
    End With
End Sub
End Sub
If you want to lock the cells, the "Locked" property should be set to "True".
 
Last edited:
Upvote 0
It's still throwing the same code. I should have also said that I am trying to run code specific to sheet one for example:
Code:
If Range("B19").Value = "Generic" Then        Rows("25:151").EntireRow.Hidden = True
        Rows("152:157").EntireRow.Hidden = False
        Rows("158:165").EntireRow.Hidden = True
    End If

It isn't allowing that to run anymore. I originally tried the code:
Code:
Sheet1.Protect Password:="abc", UserInterFaceOnly:=True
but that did't allow me to enter into some blank spaces.
 
Upvote 0
In my previous post, I accidentally pasted an extra "End Sub" line. Try:
Code:
Sub test()
    With Sheets("Sheet1")
        .Unprotect Password:="abc"
        If .Range("B19").Value = "Generic" Then
            .Rows("25:151").EntireRow.Hidden = True
            .Rows("152:157").EntireRow.Hidden = False
            .Rows("158:165").EntireRow.Hidden = True
        End If
        .Protect Password:="abc"
    End With
End Sub
Change the sheet name (in red) to suit your needs. Please note that the macro unprotects the sheet, hides or unhides the rows and then protects it again.
 
Upvote 0
Awesome. I got it to work for that section and the rest. I was having difficulty with one part and trying to put it in.
Code:
If Not Intersect(Target, Range("B19")) Is Nothing Then
            .Range("B17").ClearContents
End If

I don't know how to put that inside of the if statement. What that does is clear cells if a cell value changes. I don't know where I would put the "."
 
Upvote 0
It looks like you want to clear B17 if B19 changes. If this is correct, do you change B19 manually or is the value in B19 the result of a formula?
 
Upvote 0
Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Each time you change the value in B19 and press the RETURN key, B17 will be cleared.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B19")) Is Nothing Then Exit Sub
    Range("B17").ClearContents
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top