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.
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,744
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,744
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,744
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,744
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,089,297
Messages
5,407,442
Members
403,143
Latest member
CTremblay

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top