check Isnumeric, doesnot work well

[helmekki]

Hi

my problem is when placing a number begining wiht a letter (p80) thi
code does recognise it as a number so the macro take it as it is, bu
this couses a problem to me becasue i do want the macro to discard th
number if it contains some numbers and some letters.
however, the code works fins when i put only a number (Ex: 800)

sub test()
Dim vInput As String
Dim myRow As Range

vInput = InputBox("Please Write Your Item", "Add New Item")
If IsNumeric(vInput) Then
MsgBox ("Please Enter An Item")
vInput = InputBox("Please Write Your Item", "Add New Item")
End If
End sub

yours hesha

 

[Tom Ogilvy]

Use the Excel input box rather than the VBA inputbox

Dim vInput as Long
vInput = Appliction.Inputbox("Please Write Your Item, "Add New Item", _
Type:=1)

this will only accept numbers.

but, isnumeric doesn't return true for strings that are not just numbers

? isnumeric("B80")
False

 

[helmekki]

ok, i did Dim the vInput as Long and
use your code , so it accepts only a number

i want it only to accept letters no numbers not even a word tha
contains numbers (pp80)...........

i tried to set type:=2.........(text) , but...........

And also, the Isnumeric (vInput) when vInput = pp80 return true
i tried it many times only if vInput =whole number 0-9, but pp80
does contain letters and numbrs, so isnumeric return true

try it and see..............

any idea thank u in advanc

 

[Tom Ogilvy]

Dim vInput as Long
vInput = pp80

sets vInput equal to zero since pp80 is an un-initialized, undeclared
variable. It is not the string "pp80"

Isnumeric("pp80")

will return false.

dim bAllText as Boolean
dim vInput as String, sStr as String
Dim sChar as String, msg as String
Dim i as Long
msg = "Enter Only Letters"
Do
vInput = InputBox(msg)
sStr = ucase(vInput)
if len(trim(sStr)) = 0 then exit sub
bAllText = True
for i = 1 to len(sStr)
sChar = mid(sStr,i,1)
if asc(sChar) >= 48 and asc(sChar) <= 57 then
bAllText = False
exit for
end if
Next i
msg = "You entered at least one number, Letters only"
Loop while not bAllText

msgbox vInput