Trying to make cells numeric only

  • Thread starter Thread starter mikewild2000
  • Start date Start date
M

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
 
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?
 
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
 
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
 
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
 
Back
Top