Cell entry limit to 00000 through 99999

Hi all, how to use vba in excel to limit cell entries to numbers from 00000 through 99999?
Thank You,
Steve
steve smithAsked:
Who is Participating?
 
Ejgil HedegaardConnect With a Mentor Commented:
00000 is not a number, but a text.
To input it, the cell must be formatted as text.
If not, Excel will convert it to the number 0
You could use a custom validation rule like =AND(ISNUMBER(A1*1),LEN(A1)=5)

If you want to use VBA it could be something like this
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Integer, j As Integer
    If Target.Count = 1 Then
        If Len(Target.Value) = 5 Then
            For i = 1 To 5
                If Asc(Mid(Target.Value, i, 1)) >= 48 And Asc(Mid(Target.Value, i, 1)) <= 57 Then
                    j = j + 1
                End If
            Next i
        End If
    End If
    If j <> 5 Then
        MsgBox "Wrong input"
        Application.EnableEvents = False
        Target.ClearContents
        Application.EnableEvents = True
    End If
End Sub

Open in new window

0
 
AlanConsultantCommented:
Hi,

No need to use VBA - you can do it with data validation.

If you want to control if from VBA, the setup the data validation through there.

Hope that helps,

Alan.
0
 
Rgonzo1971Commented:
Hi,

pls try

Regards
EE20180127.xlsx
0
 
steve smithAuthor Commented:
Thank very much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.