Displaying MsgBox when range of cells have not a specific length

KevinMMO

New Member
Joined
Mar 2, 2018
Messages
17
I have a program in which a label is scanned and that value goes into a cell, but for security reasons, it's necessary to check that the length of this is always the same (for example: 8, label=12345-78) the string has a "-" in it. What I want the program to do is that, when the scanned label has a number different than this one, display a messagebox that indicates that the number is invalid and then erase the cell's content. I would really appreciate some help with this.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Code that reads cells and makes them string to apply condition

What I'm looking to do is a macro that reads a range of cells and if a string of numbers or letters different than 8 is pasted, display a message. There's a range from B9 to B20 where a scanner reads a label and inserts in order a number, so I'm also looking that it doesn't pay attention to blank cells in the range. I can't apply data validation since the data gets copied and not typed. Here's what I have so far:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)



Dim i As Integer


On Error GoTo NoCells

i = Worksheets("HojadeInspection").Range("B9:B20").Cells. _
     SpecialCells(xlCellTypeConstants).Count

If Not Len(Range("B" & CStr(i))) = 8 Then

MsgBox "La longitud del dato insertado es incorrecta, revisar nuevamente", vbCritical



End If




NoCells:
Resume Next



    
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,900
Messages
6,175,276
Members
452,629
Latest member
SahilPolekar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top