validating data in a macro?

[timcah]

Hi Everyone,

I'm trying to modify a spreadsheet which needs to incorporate some macro functions (I haven’t done these for years and even then it was only basic vb at uni). What I want to know is how to create a macro which will run the following checks and if possible (is it?) to flag any data inputted which isn’t correct.

So say I have the following 3 cells

Description (max string of 250), can you do a macro which flags if input is greater than 250?, if so can you highlight the error in a particular colour?

Date created – date field which needs to be in yyyy-mm-dd format, I’ve been playing around with a few macros and still can’t get one to work for this, Is there a macro which would convert the format (if entered e.g as 12/12/2009) or if text is inputted can an error flag it or can it only accept the date format?

Strategy – I have a couple of fields whereby there is only certain data that can be accepted (E.G, High, Medium, Low, None), I initially did this via a validation list but then there would be problems if in the future someone enters data which isn’t one of the specified. So is there a macro which can be created that will only accept certain fields or if it does accept another field that it then flags it (colour highlight again?)

I’m not sure if that makes sense!, have been reading online and trying various things the past few days and still am getting nowhere!.

Thanks Guys,

Timmy

[royUK]

The first part does not need vba at all. Use Conditional format. If you are checking Column A then use in A1 conditinal formatting -> formula is Len(A1)>250 and add a format. Copy down. More details on conditional formatting here

http://www.excel-it.com/excel_condit...formatting.htm

Noi VBA required for forcing dates. Use Data Validation from the data menu & allow dates.

[royUK]

The first part does not need vba at all. Use Conditional format. If you are checking Column A then use in A1 conditinal formatting -> formula is Len(A1)>250 and add a format. Copy down. More details on conditional formatting here

http://www.excel-it.com/excel_condit...formatting.htm

No VBA required for forcing dates. Use Data Validation from the data menu & allow dates.

[timcah]

Thanks Roy, the problem I have is data is going to be cut and pasted into the spreadsheet so will therefore cancel out the
conditional formatting, is there anyway around this?

[royUK]

To limit the input to 250 characters

Please Login or Register  to view this content.

To limit to dates

Please Login or Register  to view this content.