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.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,425
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:

ERed1

Board Regular
Joined
Jun 26, 2019
Messages
104
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.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,425
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.
 

ERed1

Board Regular
Joined
Jun 26, 2019
Messages
104
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 "."
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,425
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

Board Regular
Joined
Jun 26, 2019
Messages
104
It is manual
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,425
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
 

Forum statistics

Threads
1,077,662
Messages
5,335,564
Members
399,025
Latest member
alce

Some videos you may like

This Week's Hot Topics

Top