Locking specific cells

[ERed1]

Hi all,

I need some help fixing this 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.

 

[mumps]

Try:

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".

 

[ERed1]

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:

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:

Sheet1.Protect Password:="abc", UserInterFaceOnly:=True

but that did't allow me to enter into some blank spaces.

 

[mumps]

In my previous post, I accidentally pasted an extra "End Sub" line. Try:

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.

 

[ERed1]

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.

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 "."

 

[mumps]

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?

 

[ERed1]

It is manual

 

[mumps]

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.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B19")) Is Nothing Then Exit Sub
    Range("B17").ClearContents
End Sub

 

[ERed1]

I got it to work. Thanks for all of the help

 

[mumps]

You are very welcome.