VBA Run-time error '1004':

Bradley P. Jans 0 Reputation points
2023-09-14T14:56:37.2966667+00:00

Encountering Run-time error '1004': Method 'Range' of object '_Worksheet' failed

New to VBA Note: debug highligts the Locked = True lines of code. no merged cells in these ranges:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$L$6" Then

If Range("l6").Value = 0 Then

Sheet4.Unprotect

Sheet4.Range("K12:O31").Locked = False

Sheet4.Range("K12:O31").ClearContents

Sheet4.Range("K12:031").Locked = True

Sheet4.Protect

End If

If Range("l6").Value = 1 Then

Sheet4.Unprotect

Sheet4.Range("K12:O31").Locked = False

Sheet4.Range("L12:O31").ClearContents

Sheet4.Range("L12:031").Locked = True

Sheet4.Range("K12:K31").Locked = False

Sheet4.Protect

End If

If Range("l6").Value = 2 Then

Sheet4.Unprotect

Sheet4.Range("K12:O31").Locked = False

Sheet4.Range("M12:O31").ClearContents

Sheet4.Range("M12:031").Locked = True

Sheet4.Range("K12:L31").Locked = False

Sheet4.Protect

End If

If Range("l6").Value = 3 Then

Sheet4.Unprotect

Sheet4.Range("K12:O31").Locked = False

Sheet4.Range("N12:O31").ClearContents

Sheet4.Range("N12:031").Locked = True

Sheet4.Range("K12:M31").Locked = False

Sheet4.Protect

End If

If Range("l6").Value = 4 Then

Sheet4.Unprotect

Sheet4.Range("K12:O31").Locked = False

Sheet4.Range("O12:O31").ClearContents

Sheet4.Range("O12:031").Locked = True

Sheet4.Range("K12:N31").Locked = False

Sheet4.Protect

End If

If Range("l6").Value = 5 Then

Sheet4.Unprotect

Sheet4.Range("K12:O31").Locked = False

Sheet4.Protect

End If

End If

End Sub

Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,469 questions
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,500 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Barry Schwarz 2,111 Reputation points
    2023-09-15T06:03:42.4033333+00:00

    You have mistyped the argument of the Range property. Using the last one as an example, you typed Sheet4.Range("O12:031").Locked = True (zero-three-one).

    You meant to type Sheet4.Range("O12:O31").Locked = True (Oh-three-one).

    This problem exists in cases 1 through 5. Consistency is often a virtue but not in this case.

    0 comments
  2. Tanay Prasad 2,105 Reputation points
    2023-09-15T06:25:18.0666667+00:00

    Hi,

    Here are some references that can help you-

    https://community.spiceworks.com/how_to/195135-how-to-fix-run-time-error-1004-in-excel

    https://excelchamps.com/vba/runtime-error-1004/

    I hope this will help you.

    Best Regards.

    0 comments