Data Validation Problem

  • Thread starter Thread starter Hawk
  • Start date Start date
H

Hawk

I am stumped...

I'm trying to set data validation for a group of cells. I
choose to allow:

decimal...greater than...0

However, for some reason it will not allow me to enter any
value what-so-ever. I have done this many-a-time without
problems. Is it possible that my spreadsheet has become
corrupted or something. The same data validation is being
used on another worksheet within the same workbook and it
works fine. TIA...
 
Check your cell format - if it's Text, then any entry you make will be
interpreted as text rather than a number.

If so, change the cell format to a number format.
 
You hit the nail on the head...It is text. Thank you so
much...I was going nutz!

The reason it is text is that we need to see exactly what
the user typed in the cell (including trailing zeros). Is
their a number format that would accomplish this?
 
No, if a number entry is made (that is not Text), the parser converts
the number to a double precision floating point value. The display is
then totally dependent on the format of the cell, not how the entry was
made.

If the cell is preformatted to Text, n Worksheet_Change event macro
could create a number format for the cell on the fly that would keep the
number of digits. Something like this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim sInFormat As String
Dim sOutFormat As String
Dim nPos As Long
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(.Cells, Columns(1)) Is Nothing Then
If Not IsEmpty(.Value) And IsNumeric(.Value) Then
sInFormat = Trim(.Text)
If Left(sInFormat, 1) = "-" Then _
sInFormat = Mid(sInFormat, 2)
nPos = InStr(sInFormat, ".")
If nPos Then
If nPos = 1 Then
sOutFormat = "0." & _
String(Len(sInFormat) - 1, "0")
Else
sOutFormat = String(nPos - 1, "0") & "." & _
String(Len(sInFormat) - nPos - 1, "0")
End If
Else
sOutFormat = String(Len(sInFormat), "0")
End If
.NumberFormat = sOutFormat & ";-" & sOutFormat
Application.EnableEvents = False
.Value = .Value
Application.EnableEvents = True
Else
.NumberFormat = "@"
End If
End If
End With
End Sub

Note, this doesn't have much error checking, will choke if thousands
separators are added, doesn't take into account regional decimal
separators, won't handle exponential or scientific notation, etc.

Also, if numbers are overwritten, they'll keep the original format. You
could probably work around that with a worksheet_SelectionChange macro
that converted the format back to text.
 
Thanks...I'll play around with it.
-----Original Message-----
No, if a number entry is made (that is not Text), the parser converts
the number to a double precision floating point value. The display is
then totally dependent on the format of the cell, not how the entry was
made.

If the cell is preformatted to Text, n Worksheet_Change event macro
could create a number format for the cell on the fly that would keep the
number of digits. Something like this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim sInFormat As String
Dim sOutFormat As String
Dim nPos As Long
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(.Cells, Columns(1)) Is Nothing Then
If Not IsEmpty(.Value) And IsNumeric (.Value) Then
sInFormat = Trim(.Text)
If Left(sInFormat, 1) = "-" Then _
sInFormat = Mid(sInFormat, 2)
nPos = InStr(sInFormat, ".")
If nPos Then
If nPos = 1 Then
sOutFormat = "0." & _
String(Len(sInFormat) - 1, "0")
Else
sOutFormat = String(nPos - 1, "0") & "." & _
String(Len(sInFormat) - nPos - 1, "0")
End If
Else
sOutFormat = String(Len (sInFormat), "0")
End If
.NumberFormat = sOutFormat & ";-" & sOutFormat
Application.EnableEvents = False
.Value = .Value
Application.EnableEvents = True
Else
.NumberFormat = "@"
End If
End If
End With
End Sub

Note, this doesn't have much error checking, will choke if thousands
separators are added, doesn't take into account regional decimal
separators, won't handle exponential or scientific notation, etc.

Also, if numbers are overwritten, they'll keep the original format. You
could probably work around that with a
Click to expand...
worksheet_SelectionChange macro
 
Back
Top