Allow only numeric data in certain cells & only text strings in others

[mikeburg]

Any VBA code suggestion how to allow only numeric input in certain cell
& text string input in other cells of a particular worksheet other tha
using onentry. I need the VBA code to be in the sheet change cod
since other sheets exists in the same workbook.

For example:

Only allow numeric entries in cells F4 thru O5
Only allow text strings entries in cells F6 thru O6
Only allow numeric entries in cells F7 thru O7
Only allow text strings entries in cells F8 thru O8
Only allow numeric entries in cells F9 thru O9
Only allow text strings entries in cells F10 thru O10
etc.

Thanks so very much. mikebur

 

[Guest]

Why not use Data -> Validation?

 

[mikeburg]

I could, but forgive me I am new to VBA, I'm thinking of something I am
working on that the input range will change from time to time.
Therefore, if I could learn to do this with VBA variables, I would not
have to do it manually. I am just having trouble getting the syntax
down.

Thanks so much. mikeburg

 

[mikeburg]

I could, but forgive me I am new to VBA, I'm thinking of something I a
working on that the input range will change from time to time.
Therefore, if I could learn to do this with VBA variables, I would no
have to do it manually. I am just having trouble getting the synta
down.

Thanks so much. mikebur

 

[Guest]

You can dynamically change the validation in code if that is what you need.
Generally using the built in feature is better than building your own from
scratch in this type of circumstance. But if a code solution is your
preference... You want to use the on change event. Test the target value to
validate that it is ok. If it fails the validation routine then use
application.undo & target.select to roll back the change along with a message
explaining to the user the error of their ways.

 

[Guest]

How is it coming along from my last post. Are you following my suggestion or
is the suggestion more baffling than the original problem...

 

[mikeburg]

Sorry, but I am still having trouble coming up with an answer. I
thought it would
be a simple answer. I have been trying:
'For allowing numbers only
Range("F7:O7").NumberFormat = "@"

'Set Validation to Text
Range("F8:O8").Validation.Delete
Range("F8:O8").Validation.Add Type:=xlValidateTextLength, _
Operator:=xlBetween, Formula1:="0", Formula2:="15"

The problem I am having is numbers entered in Text cells F8:08 still
get totaled in with the sum total at the end of columns F, G, H, I, J,
K, L, M, & O.

I'm thinking that sheet change event code would be better but I am
still trying to figure out how. Can someone help me with above or the
sheet change event code? Thanks, mikeburg