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
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