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

Question

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

2

`...& cell.row &...` – Scott Craner Dec 11 '17 at 19:11

score 2 · Answer 1 · answered Dec 11 '17 at 19:29

2

[...] 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!"

The right thing to do here, is to use Data Validation so as to restrict the possible values a cell can take.

data validation setup dialog

You can specify an error message that Excel displays given an invalid value:

validation error setup

...and even a tooltip message when the cell is selected:

data validation tooltip setup

Here I've configured data validation for cell A1:

invalid valie!

You can do all that with VBA code (using the Range.Validation API), but really there's no need at all.

answered Dec 11 '17 at 19:29

Mathieu Guindon

69,817
8
107
235

@ScottCraner always strive to use the best hammer for the job =) – Mathieu Guindon Dec 11 '17 at 19:50
Again, I was being lazy. I knew what the best answer should be, but answered the asked question instead of the implied question. Wish I could force the change of the correct answer, but something tells me this will be one of those that the community upvotes above the the accepted answer. I would delete if I could. – Scott Craner Dec 11 '17 at 19:54
@ScottCraner nah, your answer is entirely correct - I'm not holding my breath for a [populist] badge on that one ;-) – Mathieu Guindon Dec 11 '17 at 19:56
@Mat's Mug - thanks for the suggestion. I was aware of this one as well. The think is that I am trying to remove all the data validations and just put everything in VBA – geocc Dec 19 '17 at 20:22

score 1 · Accepted Answer · answered Dec 11 '17 at 19:12

1

 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

answered Dec 11 '17 at 19:12

Scott Craner

148,073
10
49
81

That was so easy. You awesome. Thank you! – geocc Dec 11 '17 at 19:13
@geocc caveat: you can do what you want to do without writing a single line of VBA code (see answer below) – Mathieu Guindon Dec 11 '17 at 19:29
Wouldn't it be easier to get intersected range first and then checking those cell (do not do check for Nothing)? :) `For Each rngArea In Intersect(Selection, [W10:W10000]).Areas : For Each cell In rngArea: Next : Next` – JohnyL Dec 11 '17 at 19:38
@JohnyL That may be quicker in the long run, especially if the paste range has a lot of unnecessary cells. But overall Mat'sMug's answer is best. In this case I just answered the question and left the optimizing to the OP. If the OP wanted help Optimizing they can always go to CodeReview, now that the problem is fixed. – Scott Craner Dec 11 '17 at 19:42
@JohnyL feel free to put your method as a answer. I have no problem upvoting other's answers, especially if they are a better route. – Scott Craner Dec 11 '17 at 19:44
@ScottCraner OK! :) Yes, the Data Validation is better route my concern was about the code be it applied :) – JohnyL Dec 11 '17 at 19:54

JohnyL · Answer 3 · 2017-12-11T20:05:23.643

1

It would be easier to get intersected range first and then checking those cells:

Sub F()

    Dim cell As Range
    Dim rngArea As Range
    Dim rngIntersect As Range

    Set rngIntersect = Intersect(Selection, [W10:W10000])
    If rngIntersect Is Nothing Then Exit Sub

    For Each rngArea In rngIntersect.Areas
        For Each cell In rngArea
            '// The code...
        Next
    Next

End Sub

edited Dec 11 '17 at 20:05

answered Dec 11 '17 at 19:57

JohnyL

6,894
3
22
41

1

This is good but you should not be answering me but the question above. Some may find fault in the fact that you did not also answer the question put by the OP. – Scott Craner Dec 11 '17 at 20:02
Deleted your name :) – JohnyL Dec 11 '17 at 20:05
1

I upvoted, but it was not my name to which I was referring, I have no problem with being called out for my errors and wish more would do it, so I can learn. I was referring to the question put by the OP: `How can I make the code to show the message box with w10, then w11 and ... then w12?`. This does not answer the question put forth by the OP. Where I can see how to implement your suggestions with my answer, other's may not. What I am saying, is that there are those out there that may come along and see this as not answering the question on its own and downvoting. Good suggestion though. – Scott Craner Dec 11 '17 at 20:13

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

3 Answers3