(Solved) – VBA Run-time error ‘1004’: Method ‘Range’ of object ‘_Worksheet’


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
        Range("B12:B13").Locked = False
    End If
    If IsEmpty(Range("B12").Value) And IsEmpty(Range("B13").Value) Then
        Range("B11").Locked = False
        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
        Range("B15").Locked = False
    End If
    If Not IsEmpty(Range("B15").Value) Then
        Range("B14").Locked = True
        Range("B23").Value = Range("B15").Value
        Range("B14").Locked = False
    End If
    ' Dividends
    If Not IsEmpty(Range("B16").Value) Then
        Range("B17").Value = ""
        Range("B17").Locked = True
        Range("B17").Locked = False
    End If
    If Not IsEmpty(Range("B17").Value) Then
        Range("B16").Locked = True
        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!

Leave a Reply

Your email address will not be published. Required fields are marked *