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

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:

Code:

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

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.

Code:

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

Thank you!

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

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