Column header based data validation vba

[rodmic]

Good day experts.

I'm not sure if this has been attempted before, i'm new here, so if anyone of you have come across this situation before please link me to the correct thread

My question is, can we vba code data validation in a table to check the value of a column header and then allow only numeric or text in a cell?

To simplify, i have a data collection form, in the form i have a dynamic column headers that changes according to user specified selection of a attribute name, for eg: if the user selects an air conditioned, the column headers will display attributes for air conditioner, if they select a valve, then the headers will show attributes for a valve.

So in this way the attributes requires entry of only numbers or text under certain attributes like for eg if the header says voltage or Diameter, then values should only be numbers and no text.

Is there any way to validate a cell by looking up the header value to toggle data validation between only numbers and text? Keep in mind i can have a new column header to show which attributes are numbers only and which can be text.

Let me know if you need further clarifications. Looking forward to your expert answers. Thanks.

 

[Yongle]

I think you can do this without VBA

Use a helper cell (H1 in my example)
Add data validation in a cell (C2 in my example)

Use the same method as used to change header to automatically toggle the value of cell H1 between any pair of values to match input required
- I used "TEXT" and "NUMBER"

In C2 add data validation \ Allow: Custom \ with formula
=OR(AND(NOT(ISNUMBER(C2)),$H$1="TEXT"),AND(ISNUMBER(C2),$H$1="NUMBER"))

DV hurdle passed
If C2 is not a number AND H1 returns "TEXT"
OR
If C2 is a number AND H1 returns "NUMBER"

 

[rodmic]

Yes! It works I had to do the data validation for like 20 columns but it works :D Thanks a ton Yongle.