Allow only numeric value in Datagrid

[hariom26]

Hello everyone.....

I have one problem about data grid edit, after edit of perticular cell i need to restrict user to add only one period (.) with this code user can enter so many periods.

Public Function NumericKeys(KeyAscii As Integer) As Integer
Select Case KeyAscii
Case 8, 13, 48 To 57, 46
Case Else
KeyAscii = 0 'Reject everything else
End Select
OnlyNumericKeys = KeyAscii
End Function

i passed this function in my datadrid after edit event it will restrict to enter spacial character but fails to restrict more than one period,

Please help me in this

[dee-u]

You may want to have a look at this for checking numeric values...

[hariom26]

I use this function but it not work properly or may be i am using in a wrong way so can you tell me how can i call this function in my datagrid editing.
is there any other way to do it?

[Hack]

How are you using it, and what is it doing?

[isnoend07]

Few other checks

If Len(txtBox) > 0 And KeyAscii = 45 Then KeyAscii = 0 '45= "-" allow minus sign in left most position only
If KeyAscii = 8 Then Exit Sub 'Allow BachSpace key
If InStr(txtBox, ".") and KeyAscii = 46 Then KeyAscii = 0'only allow 1 decimal

[hariom26]

isnoend07....

for Textbox it is working completely but i want to use this in a data grid.

[hariom26]

Hack...

I have put this function in my module and call in edit event but when i am trying to press any key it will give me something else.

Function IsNumber(var As Variant, Optional AllowDot As Boolean, _
Optional AllowComma As Boolean, _
Optional AllowSgnL As Boolean, _
Optional AllowSgnR As Boolean, _
Optional AllowPars As Boolean, _
Optional AllowCurr As Boolean, _
Optional AllowSpcL As Boolean, _
Optional AllowSpcR As Boolean, _
Optional AllowSci As Boolean, _
Optional AllowHex As Boolean, _
Optional AllowOct As Boolean) As Boolean
If Not IsNumeric(var) Then
ElseIf AllowDot > -UBound(Split(var, ".")) Then '-- see Note below
ElseIf Not AllowComma And var Like "*,*" Then '-- thousand seperator
ElseIf Not AllowSgnL And var Like "[-+]*" Then '-- leading sign
ElseIf Not AllowSgnR And var Like "*[-+]" Then '-- trailing sign
ElseIf Not AllowPars And var Like "*(*" Then '-- parentheses as negative
ElseIf Not AllowCurr And var Like "*$*" Then '-- currency
ElseIf Not AllowSpcL And var Like " *" Then '-- leading spaces
ElseIf Not AllowSpcR And var Like "* " Then '-- trailing spaces
ElseIf Not AllowSci And var Like "*[DE]*" Then '-- Scientific
ElseIf Not AllowHex And var Like "*H*" Then '-- Hex
ElseIf Not AllowOct And var Like "*O*" Then '-- Oct
Else
IsNumber = True
End If
End Function

can you tell me how to use this function with datagrid becoz i'm not good in VB so may be i'm doing in wrong way.

[isnoend07]

isnoend07....

for Textbox it is working completely but i want to use this in a data grid.

Never used a data grid. If data grid has a keypress event then you can modify my code.
Maybe someone with experience on data grids will reply

[Rx_Mich]

This is an old question but still no good answer to it so I thought I would post one. Datagrids are not easy to trap for value types. The best I can tell you to do is to allow any input and trap the value type desired once you read the value from the grid or prior to posting to a grid cell. Attached is a snipet of code to catch up to 10 communication numbers in a datagrid in a patient profile. the rules as follows:

1. allow up to 80 spaces for the value if the value is indicated that it is an e-mail.
2. All pohone numbers must be 10 digits (area code + 7 digit phone #)
3. X is allowed in the 11th position if an extension is to follow with more enumbers.
4. phone types TE,HP,CP,BP,NP, NP,FX,EM ... telephone, home phone, cellphone, beeper, night phone, fax , e-mail


For P As Integer = 0 To Grid.RowCount - 1
DOCNEW(0).PhoneQual(P) = Grid1.Rows(P).Cells(0).Value
'transfer Grid1 cell value to my string for saving to data file
DOCNEW(0).Phone(P) = Grid1.Rows(P).Cells(1).Value + Strings.Space(80)
If DOCNEW(0).PhoneQual(P) <> "EM" Then ' if not an e-mail test for alpha and special characters not allowed
Dim phonetest As String
Dim ph As String
phonetest$ = DOCNEW(0).Phone(P)
DOCNEW(0).Phone(P) = "" 'clear the existing value
If Len(phonetest$) > 0 Then
For F As Integer = 1 To Len(phonetest$)
ph$ = Mid(phonetest$, F, 1)' march through the string looking for non-numeric parts of the string
If Asc(ph$) >= 48 And Asc(ph$) <= 57 Then
DOCNEW(0).Phone(P) = Trim(DOCNEW(0).Phone(P)) + ph$
End If
If UCase(ph$) = "X" And Len(Trim(DOCNEW(0).Phone(P))) = 10 Then '
'exception for extention indication if it is in the final 11th position of the string.
DOCNEW(0).Phone(P) = Trim(DOCNEW(0).Phone(P)) + ph$
End If
Next
End If
DOCNEW(0).Phone(P) = DOCNEW(0).Phone(P) + Strings.Space(80)
End If
Next P


You can make this a function call to make it a general application to any string:
sub main()
.....your code....
NumericTest(DOCNEW(0).Phone(P))
end sub


Function NumericTest (byref find$)
Dim test As String
Dim t As String
test$ = find$
find$ = ""
For F As Integer = 1 To Len(test$)
t$ = Mid(test$, F, 1)' march through the string looking for non-numeric parts of the string
If Asc(t$) >= 48 And Asc(t$) <= 57 Then
find$ = Trim(find$) + t$
End If
Next
find$ = find$ +strdup(80,32) ' pad with spaces to prevent chr(0) padding)
end function

[Rx_Mich]

I am going to list two routines. The first is for VB 6.0 The second is for VB 2008. There is no good information on how to trap this in VB 2008 and search engines only refer me to the VB forum for VB 6.0 Hopefully both with be beneficial to someone.

VB 6.0
You can make this on a Mouse click or Key Down event as I did. Key down has you still in the same cell box and it will reflect the values of the box you are still in. Key press... you are already down to the next cell before it reacts and will not relate to the field in question. I had to force it to call the Grid1 validate routine in order to get a positive trapping event. It doesn't go there on it's own.

Dim Cancel As Boolean


Private Sub Grid1_KeyDown(KeyCode As Integer, Shift As Integer)

If Len(grid1.TEXT) > 0 and grid1.col = 2 Then Call grid1_Validate(Cancel)' only react to the column with values not descriptions
' grid row is picked up from where you are physically at.

End Sub


Private Sub grid1_Validate(Cancel As Boolean)
If grid1.TEXT <> nothiing Then
FIND$ = ""
t$ = ""
'get rules either from database or make another column in the grid1 with "N numeric/ AN Alpha Numeric, etc rules
' I am forcing Numeric sensing here to to test
FLDTYPE = "N"
Select Case FLDTYPE
Case "N"
Cancel = True
For F = 1 To Len(grid1.TEXT)
t$ = Mid$(grid1.TEXT, F, 1)
If Asc(t$) >= 48 And Asc(t$) <= 57 Then
FIND$ = FIND$ + t$
End If
Next F
If Val(FIND$) = 0 Then
MsgBox ("price field cannot be ZERO if stock quantity greater than ZERO")
Exit Sub
Else
grid1.TEXT = FIND$
Cancel = True 'stay in the same box and not move on

End If
Cancel = False
grid1.TEXT = FIND$
End Select
End If
End Sub




VB2008:

VB2008 works better at this because it does trap the event. I set all the columns to frozen except the one for data. VB2008 has 2 traping events, Validated and Validating. Use the Validating to remain in the same cell as you are interpreting othwise you have already joined a new cell before it begins to trap and you are out of luck getting the correct sensing. Again, I use one column to hold the rules for sensing. In this case, the 6th column but listed as column 5 since it starts at 0.


Private Sub Grid1_RowValidating(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellCancelEventArgs) Handles Grid1.RowValidating
If Len(Grid1.Rows(e.RowIndex).Cells(3).Value) > 0 Then

Dim test As String
Dim t As String
test$ = ""
t$ = ""
test$ = Grid1.Rows(e.RowIndex).Cells(3).Value
FIND$ = ""
Select Case Trim(Grid1.Rows(e.RowIndex).Cells(5).Value) ' column with the sensing on how to treat the input into Col 3
Case "A" 'Alphanumeric
For F As Integer = 1 To Len(test$)
t$ = Mid(test$, F, 1)
If Asc(t$) = 32 And Asc(t$) >= 65 And Asc(t$) <= 122 Then
FIND$ = FIND$ + t$
End If
If Asc(t$) = 0 Then ' strip null characters and replace with space
FIND$ = FIND$ + Chr(32)
End If
Next
Case "N" ' Numeric
For F As Integer = 1 To Len(test$)
t$ = Mid(test$, F, 1)
If Asc(t$) >= 48 And Asc(t$) <= 57 Then
FIND$ = FIND$ + t$
End If
If Asc(t$) = 0 Then ' strip null characters and replace with space
FIND$ = FIND$ + Chr(32)
End If
Next
Case "NS" ' Numeric Signed (special character in place of the hundreths colum to equal a number and + or - value
For F As Integer = 1 To Len(test$)
t$ = Mid(test$, F, 1)
If Asc(t$) >= 48 And Asc(t$) <= 57 Then
FIND$ = FIND$ + t$
End If
If Asc(t$) = 0 Then ' strip null characters and replace with space
FIND$ = FIND$ + Chr(32)
End If
Next
Case "DT" ' Date and force date as MMYYDDDD
For F As Integer = 1 To Len(test$)
t$ = Mid(test$, F, 1)
If Asc(t$) >= 48 And Asc(t$) <= 57 Then
FIND$ = FIND$ + t$
End If
If Asc(t$) = 0 Then ' strip null characters and replace with space
FIND$ = FIND$ + Chr(32)
End If

Next
If Len(FIND$) <> 8 Then

MsgBox("Date field must be 8 digits long or empty")
Grid1.Rows(e.RowIndex).Cells(3).Style.ForeColor = Color.Red
Else
If Len(FIND$) = 8 Then
Select Case Val(Mid(FIND$, 1, 2))

Case Is <= 12
FIND$ = Mid(FIND$, 1, 2) + Mid(FIND$, 3, 2) + Mid(FIND$, 5, 4)
Case Else
FIND$ = Mid(FIND$, 5, 2) + Mid(FIND$, 7, 2) + Mid(FIND$, 1, 4)
End Select
Grid1.Rows(e.RowIndex).Cells(3).Value = FIND$
Grid1.Rows(e.RowIndex).Cells(3).Style.ForeColor = Color.Blue
End If
End If
Case Else

For F As Integer = 1 To Len(test$)
t$ = Mid(test$, F, 1)
If Asc(t$) = 0 Then ' strip null characters and replace with space
FIND$ = FIND$ + Chr(32)
Else
FIND$ = FIND$ + t$
End If

Next
End Select
Grid1.Rows(e.RowIndex).Cells(3).Value = FIND$
End If



End Sub




In both languages, you can trap outside of the grid, treat it and reinsert as I have indicated in my earlier post. This just gives a way to do it inside of the grid itself. Sorry if the VB 2008 side of this not applicable but thought I would give others searching for houw to do this a leg up.

Bob

[Rx_Mich]

I am going to list two routines. The first is for VB 6.0 The second is for VB 2008. There is no good information on how to trap this in VB 2008 and search engines only refer me to the VB forum for VB 6.0 Hopefully both with be beneficial to someone.

VB 6.0
You can make this on a Mouse click or Key Down event as I did. Key down has you still in the same cell box and it will reflect the values of the box you are still in. Key press... you are already down to the next cell before it reacts and will not relate to the field in question. I had to force it to call the Grid1 validate routine in order to get a positive trapping event. It doesn't go there on it's own.

Dim Cancel As Boolean


Private Sub Grid1_KeyDown(KeyCode As Integer, Shift As Integer)

If Len(grid1.TEXT) > 0 and grid1.col = 2 Then Call grid1_Validate(Cancel)' only react to the column with values not descriptions
' grid row is picked up from where you are physically at.

End Sub


Private Sub grid1_Validate(Cancel As Boolean)
If grid1.TEXT <> nothiing Then
FIND$ = ""
t$ = ""
'get rules either from database or make another column in the grid1 with "N numeric/ AN Alpha Numeric, etc rules
' I am forcing Numeric sensing here to to test
FLDTYPE = "N"
Select Case FLDTYPE
Case "N"
Cancel = True
For F = 1 To Len(grid1.TEXT)
t$ = Mid$(grid1.TEXT, F, 1)
If Asc(t$) >= 48 And Asc(t$) <= 57 Then
FIND$ = FIND$ + t$
End If
Next F
If Val(FIND$) = 0 Then
MsgBox ("price field cannot be ZERO if stock quantity greater than ZERO")
Exit Sub
Else
grid1.TEXT = FIND$
Cancel = True 'stay in the same box and not move on

End If
Cancel = False
grid1.TEXT = FIND$
End Select
End If
End Sub




VB2008:

VB2008 works better at this because it does trap the event. I set all the columns to frozen except the one for data. VB2008 has 2 traping events, Validated and Validating. Use the Validating to remain in the same cell as you are interpreting othwise you have already joined a new cell before it begins to trap and you are out of luck getting the correct sensing. Again, I use one column to hold the rules for sensing. In this case, the 6th column but listed as column 5 since it starts at 0.


Private Sub Grid1_RowValidating(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellCancelEventArgs) Handles Grid1.RowValidating
If Len(Grid1.Rows(e.RowIndex).Cells(3).Value) > 0 Then

Dim test As String
Dim t As String
test$ = ""
t$ = ""
test$ = Grid1.Rows(e.RowIndex).Cells(3).Value
FIND$ = ""
Select Case Trim(Grid1.Rows(e.RowIndex).Cells(5).Value) ' column with the sensing on how to treat the input into Col 3
Case "A" 'Alphanumeric
For F As Integer = 1 To Len(test$)
t$ = Mid(test$, F, 1)
If Asc(t$) = 32 And Asc(t$) >= 65 And Asc(t$) <= 122 Then
FIND$ = FIND$ + t$
End If
If Asc(t$) = 0 Then ' strip null characters and replace with space
FIND$ = FIND$ + Chr(32)
End If
Next
Case "N" ' Numeric
For F As Integer = 1 To Len(test$)
t$ = Mid(test$, F, 1)
If Asc(t$) >= 48 And Asc(t$) <= 57 Then
FIND$ = FIND$ + t$
End If
If Asc(t$) = 0 Then ' strip null characters and replace with space
FIND$ = FIND$ + Chr(32)
End If
Next
Case "NS" ' Numeric Signed (special character in place of the hundreths colum to equal a number and + or - value
For F As Integer = 1 To Len(test$)
t$ = Mid(test$, F, 1)
If Asc(t$) >= 48 And Asc(t$) <= 57 Then
FIND$ = FIND$ + t$
End If
If Asc(t$) = 0 Then ' strip null characters and replace with space
FIND$ = FIND$ + Chr(32)
End If
Next
Case "DT" ' Date and force date as MMYYDDDD
For F As Integer = 1 To Len(test$)
t$ = Mid(test$, F, 1)
If Asc(t$) >= 48 And Asc(t$) <= 57 Then
FIND$ = FIND$ + t$
End If
If Asc(t$) = 0 Then ' strip null characters and replace with space
FIND$ = FIND$ + Chr(32)
End If

Next
If Len(FIND$) <> 8 Then

MsgBox("Date field must be 8 digits long or empty")
Grid1.Rows(e.RowIndex).Cells(3).Style.ForeColor = Color.Red
Else
If Len(FIND$) = 8 Then
Select Case Val(Mid(FIND$, 1, 2))

Case Is <= 12
FIND$ = Mid(FIND$, 1, 2) + Mid(FIND$, 3, 2) + Mid(FIND$, 5, 4)
Case Else
FIND$ = Mid(FIND$, 5, 2) + Mid(FIND$, 7, 2) + Mid(FIND$, 1, 4)
End Select
Grid1.Rows(e.RowIndex).Cells(3).Value = FIND$
Grid1.Rows(e.RowIndex).Cells(3).Style.ForeColor = Color.Blue
End If
End If
Case Else

For F As Integer = 1 To Len(test$)
t$ = Mid(test$, F, 1)
If Asc(t$) = 0 Then ' strip null characters and replace with space
FIND$ = FIND$ + Chr(32)
Else
FIND$ = FIND$ + t$
End If

Next
End Select
Grid1.Rows(e.RowIndex).Cells(3).Value = FIND$
End If



End Sub




In both languages, you can trap outside of the grid, treat it and reinsert as I have indicated in my earlier post. This just gives a way to do it inside of the grid itself. Sorry if the VB 2008 side of this not applicable but thought I would give others searching for houw to do this a leg up.

Bob

[isnoend07]

Thanks for adding that. I do not use the data grid now, but may in an upcoming vb6 project. I will bookmark your code

[Schmidt]

Here's my two cents (more along the requirements of the OPs "double-point-suppression"...

What the code below also shows, is the external DataSource - provided in this case by
a free-standing ADO-Recordset (also a <Tab><Shift+Tab> and <Return><Shift+Return>
Grid-Key-navigation is built-in).

Into an empty Form (with a VB6-DataGrid named 'DG' - and an ADO-Project-Reference).
(creates and shows 100 Records/Rows on 3 Dbl-Columns)

Option Explicit

Private Rs As New Recordset
 
Private Sub Form_Load()
Dim i As Long

  For i = 1 To 3: Rs.Fields.Append "Dbl" & i, adDouble: Next
  Rs.Open
  For i = 1 To 100: Rs.AddNew Array("Dbl1", "Dbl2", "Dbl3"), Array(i + 0.1, i + 0.2, i + 0.3): Next
  
  Set DG.DataSource = Rs
End Sub

Private Sub Form_Resize()
  DG.Move 0, 0, ScaleWidth, ScaleHeight
End Sub

Private Sub DG_KeyDown(KeyCode As Integer, Shift As Integer)
  On Error Resume Next
    If KeyCode = 9 Then DG.Col = DG.Col + IIf(Shift, -1, 1)
    If KeyCode = 13 Then DG.Row = DG.Row + IIf(Shift, -1, 1): DG.EditActive = True
  On Error GoTo 0
End Sub

Private Sub DG_KeyPress(KeyAscii As Integer)
  Select Case KeyAscii
    Case 8, 48 To 57
    Case 46: If InStr(DG.Text, ".") Then KeyAscii = 0
    Case Else: KeyAscii = 0
  End Select
End Sub

Olaf