CODEWITHSUNDEEP
vbaexcel
Gexas
Gexas

Reputation: 678

Worksheet_change event triggers despite conditions not met

I have function which marks column D cell change time. Now I want to add another function to worksheet_change event, which would change column E accordingly if specific text is written in column D cell.

Problem I encountered, that column E gets changed every time, despite the fact that conditions are not met (other text is written to column D cell). Can somebody point me to right direction, I am quite new to VBA so maybe I miss something.

My code:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim xCellColumn As Integer
Dim xTimeColumn As Integer
Dim xPaymentColumn As Integer
Dim xRow, xCol As Integer

xCellColumn = 4
xTimeColumn = 23
xPaymentColumn = 5

'part for marking date, works as intended
On Error Resume Next
xRow = Target.Row
xCol = Target.Column

If Target.Text <> "" Then
    If xCol = xCellColumn Then
       Cells(xRow, xTimeColumn) = Now()
    End If
End If

'part for changing column E according column D selection 
'I would like this part to trigger only if text cell in column D changed _
 to "Send request" or "Start evaluation", if any other text is written in column D, _
  column E should remain intact

If Target.Text = "Send request" Or "Start evaluation" Then
    If xCol = xCellColumn Then
        Cells(xRow, xPaymentColumn) = "Yes"
    End If
End If
End Sub

Upvotes: 1

Views: 213

Answers (1)

FunThomas
FunThomas

Reputation: 29592

Change your If to 

If Target.Text = "Send request" Or Target.Text = "Start evaluation" Then

Your current if-Condition throws an error Type mismatch, but you're eating up all errors with the On Error Resume Next- message. And so VBA does what you ask: Ignore the runtime error and happily execute the next statement - which is the statement within the If-block.

A general advice: Never use On Error Resume Next

Upvotes: 5

Related Questions