0

When I change a value in the column specified (column 19), the code takes a while to run but seems to work properly, then throws the error. This also causes Excel to crash repeatedly as I'm trying to debug the code.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 19 Then
        If Target <> "yes" Then
            Target = "no"
        End If
    End If
End Sub

I've tried changing Target to activecell, using explicit references, etc and everything seems to throw the same error and crash Excel.

Any Ideas?

Pirate Richie
  • 1
  • 2
  • 1
    `Target = "no"` causes the event to fire in an infinite loop: https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure – BigBen Mar 05 '20 at 18:14
  • `Target <> "yes"` will fail if `Target` corresponds to a multi-cell range. – BigBen Mar 05 '20 at 18:14
  • @BigBen changing to `If Target <> "yes" And Target <> "no" Then` did the trick! Your other comment is another issue I didn't think of, but it would take more work to fix it than its worth, so I'll just keep that in mind. Thanks! That makes sense, the `Worksheeet_Change` method is firing itself by changing the worksheet. Edit: the link you provided makes it quite easy actually, thanks again! – Pirate Richie Mar 05 '20 at 18:24

1 Answers1

0

Do you want to change every not "yes" element to "no"?

Sub stringToNo()
    columnNumber = 19
    lastrow = fLastRow

    For i = 1 To lastrow
        If Cells(i, columnNumber) <> "yes" Then
            Cells(i, columnNumber) = "no"
        End If
    Next i
End Sub
Function fLastRow() As Long
    fLastRow = 0
    If WorksheetFunction.CountA(Cells) > 0 Then
        fLastRow = Cells.Find(What:="*", After:=[A1], _
              SearchOrder:=xlByRows, _
              SearchDirection:=xlPrevious).Row
    End If
End Function
Sandor Erdei
  • 26
  • 1
  • 4
  • I'm mainly using this so that if I want to remove a "yes", I can just press delete on the cell and it will change it back to a "no", or I suppose If someone tries to enter an obscure value in the column it just stays "no" – Pirate Richie Mar 05 '20 at 18:29