Greetings!

How can I validate the cell value like 123.12345 or 123.12345.123 or
123.12345.123.123? The numbers can be different.

Thanks.

What is your rule for a string of numbers being a valid entry - your example and "the numbers can be
different" is not enough to base even a guess on.

HTH,
Bernie
MS Excel MVP


"luvgreen" <luvgreen@discussions.microsoft.com> wrote in message
news:546A4903-B1A7-4359-AAF2-A58638DF9F93@microsoft.com...
> Greetings!
>
> How can I validate the cell value like 123.12345 or 123.12345.123 or
> 123.12345.123.123? The numbers can be different.
>
> Thanks.

Thank you for looking.

It should be like "XXX.XXXXX" or "XXX.XXXXX.XXX" or "XXX.XXXXX.XXX.XXX" or
"XXX.XXXXX.XXX.XXX.XXX"

X represents number. The first 3 numbers followed by a dot and 5 numbers,
after that it is always followed by a dot and 3 numbers. How can I validate
that? Thanks.

"Bernie Deitrick" wrote:

> What is your rule for a string of numbers being a valid entry - your example and "the numbers can be
> different" is not enough to base even a guess on.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "luvgreen" <luvgreen@discussions.microsoft.com> wrote in message
> news:546A4903-B1A7-4359-AAF2-A58638DF9F93@microsoft.com...
> > Greetings!
> >
> > How can I validate the cell value like 123.12345 or 123.12345.123 or
> > 123.12345.123.123? The numbers can be different.
> >
> > Thanks.
>
>
>

Instead of using the built in Data Validation, you can roll your own using
VBA and the worksheet change event. Copy the code below, right click on the
sheet tab, select "View Code", and paste the code in the window that
appears. The code is written to validate entries in column B, but that can be
easily changed (as can the message). The code may not be very readable
because of the continuatuons I used to defeat line wrapping errors, but it works.

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCode As String
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Range("B:B"), Target) Is Nothing Then
Application.EnableEvents = False
myCode = Target.Value
If (myCode Like _
"[0-9][0-9][0-9].[0-9][0-9][0-9][0-9][0-9]") _
Or (myCode Like _
"[0-9][0-9][0-9].[0-9][0-9][0-9][0-9][0-9]." & _
"[0-9][0-9][0-9]") _
Or (myCode Like _
"[0-9][0-9][0-9].[0-9][0-9][0-9][0-9][0-9]." & _
"[0-9][0-9][0-9].[0-9][0-9][0-9]") _
Or (myCode Like _
"[0-9][0-9][0-9].[0-9][0-9][0-9][0-9][0-9]." & _
"[0-9][0-9][0-9].[0-9][0-9][0-9].[0-9][0-9][0-9]") Then
Else
Application.Undo
MsgBox "That was an invalid entry in cell " _
& Target.Address(False, False) & "."
End If
Application.EnableEvents = True
End If
End Sub



"luvgreen" <luvgreen@discussions.microsoft.com> wrote in message
news:079ED5D8-B985-40FA-AD6B-8919C8FE58B1@microsoft.com...
> Thank you for looking.
>
> It should be like "XXX.XXXXX" or "XXX.XXXXX.XXX" or "XXX.XXXXX.XXX.XXX" or
> "XXX.XXXXX.XXX.XXX.XXX"
>
> X represents number. The first 3 numbers followed by a dot and 5 numbers,
> after that it is always followed by a dot and 3 numbers. How can I validate
> that? Thanks.
>
> "Bernie Deitrick" wrote:
>
>> What is your rule for a string of numbers being a valid entry - your example and "the numbers can
>> be
>> different" is not enough to base even a guess on.
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>> "luvgreen" <luvgreen@discussions.microsoft.com> wrote in message
>> news:546A4903-B1A7-4359-AAF2-A58638DF9F93@microsoft.com...
>> > Greetings!
>> >
>> > How can I validate the cell value like 123.12345 or 123.12345.123 or
>> > 123.12345.123.123? The numbers can be different.
>> >
>> > Thanks.
>>
>>
>>

Thank you much!!!

"Bernie Deitrick" wrote:

> Instead of using the built in Data Validation, you can roll your own using
> VBA and the worksheet change event. Copy the code below, right click on the
> sheet tab, select "View Code", and paste the code in the window that
> appears. The code is written to validate entries in column B, but that can be
> easily changed (as can the message). The code may not be very readable
> because of the continuatuons I used to defeat line wrapping errors, but it works.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim myCode As String
> If Target.Cells.Count > 1 Then Exit Sub
> If Not Intersect(Range("B:B"), Target) Is Nothing Then
> Application.EnableEvents = False
> myCode = Target.Value
> If (myCode Like _
> "[0-9][0-9][0-9].[0-9][0-9][0-9][0-9][0-9]") _
> Or (myCode Like _
> "[0-9][0-9][0-9].[0-9][0-9][0-9][0-9][0-9]." & _
> "[0-9][0-9][0-9]") _
> Or (myCode Like _
> "[0-9][0-9][0-9].[0-9][0-9][0-9][0-9][0-9]." & _
> "[0-9][0-9][0-9].[0-9][0-9][0-9]") _
> Or (myCode Like _
> "[0-9][0-9][0-9].[0-9][0-9][0-9][0-9][0-9]." & _
> "[0-9][0-9][0-9].[0-9][0-9][0-9].[0-9][0-9][0-9]") Then
> Else
> Application.Undo
> MsgBox "That was an invalid entry in cell " _
> & Target.Address(False, False) & "."
> End If
> Application.EnableEvents = True
> End If
> End Sub
>
>
>
> "luvgreen" <luvgreen@discussions.microsoft.com> wrote in message
> news:079ED5D8-B985-40FA-AD6B-8919C8FE58B1@microsoft.com...
> > Thank you for looking.
> >
> > It should be like "XXX.XXXXX" or "XXX.XXXXX.XXX" or "XXX.XXXXX.XXX.XXX" or
> > "XXX.XXXXX.XXX.XXX.XXX"
> >
> > X represents number. The first 3 numbers followed by a dot and 5 numbers,
> > after that it is always followed by a dot and 3 numbers. How can I validate
> > that? Thanks.
> >
> > "Bernie Deitrick" wrote:
> >
> >> What is your rule for a string of numbers being a valid entry - your example and "the numbers can
> >> be
> >> different" is not enough to base even a guess on.
> >>
> >> HTH,
> >> Bernie
> >> MS Excel MVP
> >>
> >>
> >> "luvgreen" <luvgreen@discussions.microsoft.com> wrote in message
> >> news:546A4903-B1A7-4359-AAF2-A58638DF9F93@microsoft.com...
> >> > Greetings!
> >> >
> >> > How can I validate the cell value like 123.12345 or 123.12345.123 or
> >> > 123.12345.123.123? The numbers can be different.
> >> >
> >> > Thanks.
> >>
> >>
> >>
>
>
>