Private Function Code Change

As part of a private function this code requires a value 1 to 12. How would
this code be modified to require the user to enter a value with two decimals.
For example 123.98. Blanks, non-number, and integer requirements are
already in place and working.

' Between 1 and 12?
If cell < 1 Or cell > 12 Then
EntryIsValid = "Valid values are between 1 and 12."
Exit Function
End If

I assume that data validation (Excel menu: Data-> Validation) on the
worksheet is not an option; therefore, how about checking that the
third character from the right is a decimal point? Something like
if mid(cell, len(cell)-2, 1) <> "."

Function EntryIsValid(cell As Range)
With cell
EntryIsValid = _
.Value >= 1 And _
.Value <= 12 And _
Round(.Value, 2) = .Value
End With
End Function

HTH
--
AP

"Phil H" <PhilH@discussions.microsoft.com> a écrit dans le message de
news:A93A94CA-3375-4218-8AAD-691884FA5714@microsoft.com...
> As part of a private function this code requires a value 1 to 12. How
would
> this code be modified to require the user to enter a value with two
decimals.
> For example 123.98. Blanks, non-number, and integer requirements are
> already in place and working.
>
> ' Between 1 and 12?
> If cell < 1 Or cell > 12 Then
> EntryIsValid = "Valid values are between 1 and 12."
> Exit Function
> End If
>

Art,

The only requirement is for the entry to have two decimals, whatever the
number. here is what I did, using your and Ardus' replies:

' Two decimal entry required
If Mid(cell, Len(cell) - 2, 1) <> "." Then
EntryIsValid = "Two-decimal entry required."
Exit Function
End If

I get a Run-time error '5': Invalid procedure call or argument, with the
line If Mid... highlighted yellow.


"Art H" wrote:

> I assume that data validation (Excel menu: Data-> Validation) on the
> worksheet is not an option; therefore, how about checking that the
> third character from the right is a decimal point? Something like
> if mid(cell, len(cell)-2, 1) <> "."
>
>

I'm sorry that I don't understand the context. Maybe "cell" is not
properly defined. Using ThisWorkbook and the change event, the
following code does the simple test and outputs a message--nothing
more.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
If Mid(Target.Value, Len(Target.Value) - 2, 1) <> "." Then MsgBox
"Invalid"
End Sub

HTH,

P.S. You might need to add error checking in case the input is less
than three characters. Mid does not like the second parameter to be
less than 1.