Search code examples
vbaexcelrow-numbermsgbox

Message box to take the row number when multiple errors are shown


I am using this code to receive an error message every time when in column "W" a text is inserted. When this happens the text is deleted and a box message appears:"The row W" & r & " must contain only digits!" which tells the row number of the error. r - is set as Target.Row

My problem is that, when I copy a text in the range w10:w12, I receive the error message 3 times, which is great. But, in the message box it shows only row number w10 - 3 times i.e."The row W10 must contain only digits!" . How can I make the code to show the message box with w10, then w11 and lastly then w12?

 Private Sub Worksheet_Change(ByVal Target As Range) Dim cell As Range
 Dim r As Long

 r = Target.Row

 Application.EnableEvents = False
 For Each cell In Target
     If Not Application.Intersect(cell, Range("w10:w10000")) Is Nothing Then   
        If Not IsNumeric(cell.Value) Then
           MsgBox "The row W" & r & " must contain only digits!"
           cell.Value = vbNullString
        End If
     End If
  Next cell
  Application.EnableEvents = True

Solution

  •  Private Sub Worksheet_Change(ByVal Target As Range) Dim cell As Range
    
         Application.EnableEvents = False
         For Each cell In Target
             If Not Application.Intersect(cell, Range("w10:w10000")) Is Nothing Then   
                If Not IsNumeric(cell.Value) Then
                   MsgBox "The row W" & cell.row & " must contain only digits!"
                   cell.Value = vbNullString
                End If
             End If
         Next cell
         Application.EnableEvents = True