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

Question

I know this question is similar to a lot of others posted on Stack, but none of their solutions worked for me. I'm new to VBA so please try to make your solution easy to understand. The code below essentially conditionally locks and unlock cells (not done yet; still trying to get the framework down):

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Sheet1.Protect UserInterFaceOnly:=True
    ' Volatility
    If Not IsEmpty(Range("B11").Value) Then
        Range("B12").Value = ""
        Range("B13").Value = ""
        Range("B22").Value = Range("B11").Value
        Range("B12:B13").Locked = True
    Else
        Range("B12:B13").Locked = False
    End If
    If IsEmpty(Range("B12").Value) And IsEmpty(Range("B13").Value) Then
        Range("B11").Locked = False
    Else
        Select Case Range("B12").Value
            Case Is = "Daily"
                Range("B22").Value = Range("B13").Value * Sqr(252)
            Case Is = "Weekly"
                Range("B22").Value = Range("B13").Value * Sqr(52)
            Case Is = "Monthly"
                Range("B22").Value = Range("B13").Value * Sqr(12)
            Case Is = "Annual"
                Range("B22").Value = Range("B13").Value
        End Select
        Range("B11").Locked = True
    End If
    ' Time
    If Not IsEmpty(Range("B14").Value) Then
        Range("B15").Value = ""
        Range("B15").Locked = True
        Range("B23").Value = Range("B14").Value / Range("B7").Value
    Else
        Range("B15").Locked = False
    End If
    If Not IsEmpty(Range("B15").Value) Then
        Range("B14").Locked = True
        Range("B23").Value = Range("B15").Value
    Else
        Range("B14").Locked = False
    End If
    ' Dividends
    If Not IsEmpty(Range("B16").Value) Then
        Range("B17").Value = ""
        Range("B17").Locked = True
    Else
        Range("B17").Locked = False
    End If
    If Not IsEmpty(Range("B17").Value) Then
        Range("B16").Locked = True
    Else
        Range("B16").Locked = False
    End If


    Select Case Range("B6").Value
        Case Is = "Cox Rox Rubinstein (1979)"
        ' If requirements satisfied, populate outputs
        ' Else make output values blank
            Range("B24").Value = ""
        Case Is = "Forward Tree"
            Range("B24").Value = ""
        Case Is = "Lognormal Tree"
            Range("B24").Value = ""
        Case Is = "Custom"
            Range("B24").Value = ""
    End Select

End Sub

The problem is, anytime I change any cell value on the sheet Excel prompts "Getting run-time 1004: Method 'Range' of object '_Worksheet' failed" without specifying which line of code, and then force quits the program. See sheet here. Any help is greatly appreciated!

Every time you update the sheet it re-triggers your code, so consider turning off events before you start making changes. Don't forget to re-enable them before your sub exits. — Tim Williams
Ted probably pointed you to the cause of your problem. But Sheet1.Protect UserInterFaceOnly:=True sets a protection on my previously unprotected sheet. Use this line once, when you set the protection, perhaps upon opening the workbook. Remove it from the event procedure where it will be applied multiple times. — Variatus

Manny G Manny G · Accepted Answer · 2020-04-11T01:59:50

I figured out that I was fundamentally wrong in my usage of

Private Sub Worksheet_Change(ByVal Target As Range)

Without using intersect() I'm essentially addressing a range that is nonexistent. Easy fix is the following:

If Not Intersect(Target, Range("B2")) Is Nothing Then

After which, turning events on and off becomes relevant as to prevent infinite loops when changing the values of selected cells. Thanks for the tips!

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

1 Answers