Hi All,
My worksheet_change VBA code is located on Sheet1.
There is data validation on this worksheet, so if a user enters data outside the restrictions, then type in "N/A" the macro triggers 3 times.
Why is this the case? Is there any way I can solve it so it only runs once? My code is below.
I found here a claim: "Three times: once for the user's first, invalid entry; once for data-validation's restoration of the previous value; and once for the user's updated entry (or acceptance of the restored value, selecting a different cell)."
I need the workbook to function normally - E.g. Be able to still select multiple cells and delete them.
My worksheet_change VBA code is located on Sheet1.
There is data validation on this worksheet, so if a user enters data outside the restrictions, then type in "N/A" the macro triggers 3 times.
Why is this the case? Is there any way I can solve it so it only runs once? My code is below.
VBA Code:
Option Explicit
Option Compare Text
Sub Worksheet_Change(ByVal Target As Range)
Dim rInt As Range
Dim k As Range
Dim Substitute As Range
Dim C As String
Dim R As String
With Target
If .Cells.Count = 1 And Not (Application.Intersect(.Cells, Range("A14:T999")) Is Nothing) Then
If .Value = "N/A" Then
Application.EnableEvents = False
Initials_Comment_Box.TextBox1 = Target.Address
Initials_Comment_Box.Show
Application.EnableEvents = True
End If
End If
End With
End Sub
I found here a claim: "Three times: once for the user's first, invalid entry; once for data-validation's restoration of the previous value; and once for the user's updated entry (or acceptance of the restored value, selecting a different cell)."
I need the workbook to function normally - E.g. Be able to still select multiple cells and delete them.