Restricting input box entries to integers

[andreas]

Dear Experts:

below macro applies a user-defined paragraph style to rows using an
input box. The macro is running fine.
But the input box also allows for entries such as 7,2 (comma because I
live in Germany). How do I have to re-write the code to only allow
integers as input box entries?

Help is much appreciated. Thank you very much in advance.

Regards, Andreas



Sub Tbl_BodyStyle()

Dim oRng As Word.Range
Dim oTbl As Word.Table
Dim AskRowNumber As String
Dim blnAsk As Boolean

If Not Selection.Information(wdWithInTable) Then
MsgBox "Please place the cursor into the table", _
vbOKOnly + vbCritical, "User-defined style for selected table
rows"
Exit Sub
End If

Set oTbl = Selection.Tables(1)
Set oRng = oTbl.Range

blnAsk = True
Do While blnAsk
AskRowNumber = InputBox("Please indicate the first row number" &
vbCrLf & _
"to acquire user-defined paragraph style for table rows",
"style for rows x to " & oTbl.rows.Count)
If AskRowNumber = "" Then Exit Sub
If IsNumeric(AskRowNumber) Then
If AskRowNumber >= 1 And AskRowNumber <= Selection.Tables
(1).rows.Count Then blnAsk = False
End If
Loop

Set oTbl = Selection.Tables(1)
Set oRng = oTbl.Range
oRng.Start = oTbl.rows(AskRowNumber).Range.Start
oRng.Style = "user-defined-para-style"
Set oTbl = Nothing
Set oRng = Nothing
End Sub

 

[Jay Freedman]

First, stop misusing data types. AskRowNumber is a String variable, but
you're using it as a number in the statements

If AskRowNumber >= 1 And AskRowNumber <= Selection.Tables
(1).rows.Count Then blnAsk = False
and

oRng.Start = oTbl.rows(AskRowNumber).Range.Start

You're relying on VBA to convert the string internally to an integer or some
other numeric data type to do the comparison, but this is not safe.

In addition to the String variable, declare a genuine Integer variable such
as

Dim nRowNumber As Integer

After the IsNumeric test, explicitly convert the String to an Integer:

nRowNumber = Int(Val(AskRowNumber))

If the string's value contains a decimal part as in "7,2" then this
statement will assign the integer part (in this case, 7) to nRowNumber.

Then you can replace AskRowNumber by nRowNumber in both of the statements
quoted above.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[Pesach Shelniitz]

Hi Andreas,

You can test for the presence of a comma in the string that the user enters
by changing your validation conditions as follows.

If IsNumeric(AskRowNumber) Then
If InStr(1, AskRowNumber, ",", vbTextCompare) <> 0 Then
MsgBox "The number must be an integer."
ElseIf AskRowNumber >= 1 And AskRowNumber <=
Selection.Tables(1).Rows.Count Then
blnAsk = False
End If
End If

 

[andreas]

Hi Andreas,

You can test for the presence of a comma in the string that the user enters
by changing your validation conditions as follows.

    If IsNumeric(AskRowNumber) Then
        If InStr(1, AskRowNumber, ",", vbTextCompare) <> 0 Then
            MsgBox "The number must be an integer."
        ElseIf AskRowNumber >= 1 And AskRowNumber <=
Selection.Tables(1).Rows.Count Then
            blnAsk = False
        End If
    End If

--
Hope this helps,
Pesach Shelnitz
My Web site:http://makeofficework.com

"andreas" <[email protected]> ???
??????:4031f5b4-ab71-4a2b-b7d1-357d03acc...@y12g2000yqh.googlegroups.com....















- Zitierten Text anzeigen -

Hi Pesach,

great job, exactly what I wanted. Thank you very much for your
professinal help. Regards, Andreas

 

[andreas]

First, stop misusing data types. AskRowNumber is a String variable, but
you're using it as a number in the statements


You're relying on VBA to convert the string internally to an integer or some
other numeric data type  to do the comparison, but this is not safe.

In addition to the String variable, declare a genuine Integer variable such
as

  Dim nRowNumber As Integer

After the IsNumeric test, explicitly convert the String to an Integer:

  nRowNumber = Int(Val(AskRowNumber))

If the string's value contains a decimal part as in "7,2" then this
statement will assign the integer part (in this case, 7) to nRowNumber.

Then you can replace AskRowNumber by nRowNumber in both of the statements
quoted above.

--
Regards,
Jay Freedman
Microsoft Word MVP        FAQ:http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroupso
all may benefit.













- Zitierten Text anzeigen -

Hi Jay,

thank you very much for the insight into this matter. Your solution
works just fine.

Thank you very much for your terrific help. I really appreciate it.
Regards, Andreas