VBA Help - Run-time error '-2147417848 (80010108)': Method 'Value' of object 'Range'

[dinn]

Hello,

I'm getting an error when trying using this spreadsheet I have designed. The spreadsheet has four questions which are meant to provide a rough guide of the size of a user story.

What's strange is that I have written this code and it works perfectly on the spreadsheet version within my onedrive but when I send it to a colleague he gets this error:

Run-time error '-2147417848 (80010108)': Method 'Value' of object 'Range' failed. This happens even if he enables content and saves the file locally, i.e. not running it directly from the attachment in outlook.

I'm a bit of a noob when it comes to VBA but don't think what I am trying to accomplish is that difficult. So if there is a better way to write the code to achieve same results and resolve the issue then please let me know. Code is below:

Private Sub Worksheet_Change(ByVal Target As Range)


    If Target.Column = 3 And Target.Row = 3 Then
        If Target.Value = "No" Then
            Application.Rows("4:4").Select
            Application.Selection.EntireRow.Hidden = False
        ElseIf Target.Value = "Yes" Or Target.Value = "" Then
            Application.Rows("4:6").Select
            Application.Selection.EntireRow.Hidden = True
        End If
    End If
     If Target.Column = 3 And Target.Row = 4 Then
        If Target.Value = "Hard" Or Target.Value = "" Then
            Application.Rows("5:6").Select
            Application.Selection.EntireRow.Hidden = True
            Range("B7").Value = "L"
        Else
            Application.Rows("5:6").Select
            Application.Selection.EntireRow.Hidden = False
        End If
    End If
    
    If Range("C3").Value = "" Then
    If Range("C4").Value = "" Or Range("C5").Value = "" Or Range("C6").Value = "" Then
       Range("B7").Value = ""
    End If
    End If


    If Range("C3").Value = "No" Then
    If Range("C4").Value = "" Or Range("C5").Value = "" Or Range("C6").Value = "" Then
       Range("B7").Value = ""
    End If
    End If




    If Range("C3").Value = "Yes" Then
    Range("B7").Value = "XL"
    End If
    
    If Range("C4").Value = "Medium" Then
        If Range("C5").Value = "Yes" And Range("C6").Value = "Yes" Then
       Range("B7").Value = "L"
    End If
    End If
    
    If Range("C4").Value = "Medium" Then
        If Range("C5").Value = "Maybe" And Range("C6").Value = "Yes" Then
       Range("B7").Value = "L"
    End If
    End If
    
    If Range("C4").Value = "Medium" Then
        If Range("C5").Value = "Maybe" And Range("C6").Value = "No" Then
       Range("B7").Value = "L"
    End If
    End If
    
    If Range("C4").Value = "Medium" Then
        If Range("C5").Value = "No" And Range("C6").Value = "Yes" Then
       Range("B7").Value = "L"
    End If
    End If
    
    If Range("C4").Value = "Medium" Then
        If Range("C5").Value = "Yes" And Range("C6").Value = "No" Then
       Range("B7").Value = "L"
    End If
    End If
    
    If Range("C4").Value = "Medium" Then
        If Range("C5").Value = "No" And Range("C6").Value = "No" Then
       Range("B7").Value = "M"
    End If
    End If
    
    If Range("C4").Value = "Easy" Then
        If Range("C5").Value = "Yes" And Range("C6").Value = "Yes" Then
       Range("B7").Value = "M"
    End If
    End If
        
    If Range("C4").Value = "Easy" Then
        If Range("C5").Value = "Maybe" And Range("C6").Value = "Yes" Then
       Range("B7").Value = "M"
    End If
    End If
    
    If Range("C4").Value = "Easy" Then
        If Range("C5").Value = "Maybe" And Range("C6").Value = "No" Then
       Range("B7").Value = "S"
    End If
    End If
    
    If Range("C4").Value = "Easy" Then
        If Range("C5").Value = "No" And Range("C6").Value = "Yes" Then
       Range("B7").Value = "M"
    End If
    End If
    
    If Range("C4").Value = "Easy" Then
        If Range("C5").Value = "Yes" And Range("C6").Value = "No" Then
       Range("B7").Value = "M"
    End If
    End If
    
    If Range("C4").Value = "Easy" Then
        If Range("C5").Value = "No" And Range("C6").Value = "No" Then
       Range("B7").Value = "XS"
    End If
    End If


End Sub

[Artik]

The Worksheet_Change event may change the value of some cells, which causes the event to be called again. And during the second call, the next event will be called again, etc. The execution of the code falls into an infinite loop. Although newer versions of Excel (probably from version 2003) are protected for such eventuality, this situation may generate unexpected errors. To prevent re-triggering of the Worksheet_Change event, you should first disable it and finally (mandatory!) enable the event response (Application.EnableEvents).
I also changed the record of your macro slightly, reducing the number of conditions to be checked during operation.

Private Sub Worksheet_Change(ByVal Target As Range)


    Application.EnableEvents = False




    If Target.Column = 3 Then
        
        If Target.Row = 3 Then
            If Target.Value = "No" Then
                Me.Rows("4:4").Hidden = False
            ElseIf Target.Value = "Yes" Or Target.Value = "" Then
                Me.Rows("4:6").Hidden = True
            End If
        
        ElseIf Target.Row = 4 Then
        
            If Target.Value = "Hard" Or Target.Value = "" Then
                Me.Rows("5:6").Hidden = True
                Range("B7").Value = "L"
            Else
                Me.Rows("5:6").Hidden = False
            End If
            
        End If
        
    End If




    Select Case Range("C3").Value
        Case "", "No"
            If Range("C4").Value = "" Or Range("C5").Value = "" Or Range("C6").Value = "" Then
                Range("B7").ClearContents
            End If
        Case "Yes"
            Range("B7").Value = "XL"
    End Select




    If Range("C4").Value = "Medium" Then


        If Range("C6").Value = "Yes" Then


            Select Case Range("C5").Value
                Case "Yes", "Maybe", "No"
                    Range("B7").Value = "L"
            End Select


        ElseIf Range("C6").Value = "No" Then


            Select Case Range("C5").Value
                Case "Yes", "Maybe"
                    Range("B7").Value = "L"
                Case "No"
                    Range("B7").Value = "M"
            End Select


        End If


    End If




    If Range("C4").Value = "Easy" Then


        If Range("C6").Value = "Yes" Then


            Select Case Range("C5").Value
                Case "Yes", "No", "Maybe"
                    Range("B7").Value = "M"
            End Select


        ElseIf Range("C6").Value = "No" Then


            Select Case Range("C5").Value
                Case "Maybe"
                    Range("B7").Value = "S"
                Case "Yes"
                    Range("B7").Value = "M"
                Case "No"
                    Range("B7").Value = "XS"
            End Select


        End If


    End If




    Application.EnableEvents = True


End Sub

Another reason for the error may be the occurrence of a sheet error in the formulas used, if the macro refers to cells containing these formulas. Do C4, C5 and C6 cells contain formulas that can return as a result of a sheet error?

Artik

[dinn]

This has solved my issue. Thank you for your help.

I did make one change but other than that it was perfect!