Checking date input

[Michael Simpson]

First of all, my apologies is this is a ridiculously newbie question. I have written applications in VBA, but am (on a scale of 1-10) a 1 when it comes to using the basic aspects of Excel.

My question is as follows. I have been given a small workbook (cols A-J, 20-30 lines - number of lines can vary/increase). This workbook is updated by end-users who then export it as a CSV file. It is then uploaded to a mainframe and used as input there. Trouble is, of course, then they end up entering all sorts of garbage in rows/columns that they shouldn't. I was asked if I could change the workbook so that their data was checked (as much as possible) at source before uploading.

To begin with, they want to ensure that a data column only has valid dates, but I suspect the day will come when they start wanting to check other columns, both as they are and with relational checks between columns.

I have no problems in designing a userform and locking the sheet so they can only enter/change data via the form but is this the best way to go ?

Thank you

 

[Damon Ostrander]

Hi Michael,

Yes, I believe forcing the uses to enter the data properly at the beginning is always best. If you don't do this there are myriad ways the users can enter things, often in ways that you can't predict or anticipate. Not only should you check whether the users enter dates that are valid as dates, but are in a valid range, and are valid days of the week, etc. Checking things like this can save you a lot of headaches later.

Doing such checks in Excel is relatively simple, including the relational checks between columns (this is the type of thing that Excel is designed for), so I wouldn't try to hard to avoid it.

Yes, you can do it by requiring all input via a userform, but you can also do it by checking the data in each data entry cell as soon as the user enters it, and not allowing the user to proceed without fixing it. This requires a bit of VBA code, but is not difficult. Alternately you could wait until the user tries to save the workbook, then highlight all the errors, and not allow the user to save it until all errors are corrected.

Keep Excelling.

Damon

 

[Michael Simpson]

Thanks Damon. Like I say, I'm a real newbie when it comes to using Excel without using VBA code. I'll have a shot at your suggestion of using as little VBA code as possible and without using a form (you gotta learn sometime)