PDA

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



dinn
07-08-2019, 02:39 AM
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
07-08-2019, 08:02 PM
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
07-10-2019, 06:28 AM
This has solved my issue. Thank you for your help.

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