CODEWITHSUNDEEP
excelvba
Michael Amati
Michael Amati

Reputation: 3

Else statement causing run-time error

I'm writing my first VBA code and getting an odd error I can't track.

I'm simply trying to assign a value to certain cells if another cell (C15) is equal to "No" and set the values of these cells to "Other" if C15 has any other value.

It works if I comment out the else section, but fails and says

"Method 'Range' of object '_Worksheet' failed."

Then Excel crashes.

Any help is greatly appreciated. 

Option Explicit
Public Sub_Contractor_Target As Range
Public Sub_Contractor_Target_Value As String
Public SC_Option3 As String


Sub SetTarget()
    Set Sub_Contractor_Target = Worksheets("Menu").Range("C15")
    Set Sub_Contractor_Target_Value = Sub_Contractor_Target.Value
End Sub




'Set Sub Contractor Section to Option 3
Private Sub worksheet_change(ByVal Sub_Contractor_Target As Range)
SC_Option3 = Worksheets("Options").Range("D27").Value
'MsgBox (Sub_Contractor_Target)
If Sub_Contractor_Target.Value = "No" Then

   Worksheets("Menu").Range("C55") = SC_Option3
   Worksheets("Menu").Range("C56") = "Low"

Else
   Worksheets("Menu").Range("C55,C56").Value = "Other"
End If
End Sub

Upvotes: 0

Views: 41

Answers (2)

DisplayName
DisplayName

Reputation: 13386

I think you don't need most of your code (Public variables, SetTarget sub)

Try putting the following only in your "Menu" worksheet code pane

Option Explicit
Private Sub worksheet_change(ByVal Target as Range)
    If Target.Address <> "$C$15" Then Exit Sub
    On Error GoTo ExitSub
    Application.EnableEvents = False
    If Range("C15").Value = "No" Then
        Range("C55") = Worksheets("Options").Range("D27").Value
        Range("C56") = "Low"
    Else
        Range("C55,C56").Value = "Other"
    End If
ExitSub:
    Application.EnableEvents = True
End Sub

Upvotes: 0

user4039065
user4039065

Reputation:

Your Else goes into a continuous retrigger loop. Disable events when you write to the same worksheet via worksheet_change event.

'Set Sub Contractor Section to Option 3
Private Sub worksheet_change(ByVal Sub_Contractor_Target As Range)
    On Error GoTo safe_exit
    SC_Option3 = Worksheets("Options").Range("D27").Value
    'MsgBox (Sub_Contractor_Target)
    Application.EnableEvents = False
    If Sub_Contractor_Target.Value = "No" Then

       Worksheets("Menu").Range("C55") = SC_Option3
       Worksheets("Menu").Range("C56") = "Low"

    Else
       Worksheets("Menu").Range("C55,C56").Value = "Other"
    End If
safe_exit:
    Application.EnableEvents = True
End Sub

Upvotes: 2

Related Questions