Trying to make cells numeric only

[mikewild2000]

Eight celles on my worksheet need to be numeric only, and if a user
inputs text into any of the 8 cells then, the key commands (or other
method) do not work. (or accepted)

Here is what i have managed to piece together so far from the help
files.


Private Sub Worksheet_Change(ByVal Target As Range)
dim cellok as boolean
Dim vdata As Range, Cell As Range
Set vdata = Range("b5,d5,f5,h5,j5,b9,d9,f9,h9,j9")
If IsNumeric(vdata.Cell).Value Then
cellOk
Else

End If
End Sub

 

[Tom Ogilvy]

IsNumeric will not evaluate a multicell range as you appear to be trying to
do. You need to check each cell individually.

Why not just use Validation under the data menu?

 

[mikewild2000]

Tom, what about the Len function, as this does not work either.

Dim vdata As Range, Cell As Range
Set vdata = Range("c7:j7")
If Len(vdata.Cell).Value Then
exit sub
end if
msgbox "blah blah"
end if
end su

 

[Tom Ogilvy]

There are not very many VBA functions (none I can think of) that will
evaluate multiple cells as an argument.

This is an illegal construct anyway: Len(vdata.Cell).Value

len returns a number - a number certainly doesn't have a Value property.

Dim bBad as Boolean
Dim cell as Range
bBad = False
for each cell in Range("c7:j7")
if not isnumeric(cell) then
bBad = True
exit sub
Next
if bBad then
msgbox "All data must be numeric"

End if

 

[mikewild2000]

Sorry to my fault

On the numeric topic i took you advice and used data\validate from th
tools menu.

But could the len function validate that a group of cells only have
numbers or less in each cell