Excel MT #143: VBA: Avoid Data Validation 1 Rule Limit

Hi all,

I need to setup a validation rule that would permit entries like those that follow:

- 8 digit number that MUST commence with 200 (i.e. 20012345, 20034342,
etc).

- The words "Free" , "Faulty" and "Employee"

I would the user to receive an error message is any of the above is not met, buit no need for a message before entering the data.

This rule will be applied to a single column (i.e. Column A)

Would have prefered to send you how far i've got with code, but not sure how to even start this one. Not even using the Excel 2007 Data Validation wizards.

Hope you can help,

Million thanks

D

Hi

In my table, I would like to set up a validation that does not allow a time entry to be inputted if it is earlier than the time noted above or beside it. For example -

For task "B", cell E3 completes its activity at 5:30 and task "C" commences its activity at 05:00 (cell D5) - this is a conflict as each subsequent row is a separate task that cannot commence before the prior one is completed.

Also cell D8 and cell E7 are in conflict - cell D8 cannot commence prior to the time in cell E7 (8:20). Also as you read right across each row, the times must be progressive. EG: cells C8 and B8 are in conflict. The "check" time in cell C8 has to be later than the time in cell B8 (6:55).

Just to complicate this a little more - there will be some rows missing as is the case in row 4.

Hoping someone can assist.

Steve


Excel 2007 A B C D E F 1 Arrive Check Commence Complete Depart 2 A 03:00 0-Jan 03:10 0-Jan 03:20 0-Jan 04:20 0-Jan 04:30 0-Jan 3 B 03:30 0-Jan 03:40 0-Jan 04:30 0-Jan 05:30 0-Jan 05:40 0-Jan 4 5 C 04:40 0-Jan 04:50 0-Jan 05:00 0-Jan 06:00 0-Jan 06:10 0-Jan 6 D 05:00 0-Jan 05:10 0-Jan 06:10 0-Jan 07:10 0-Jan 07:20 0-Jan 7 E 05:30 0-Jan 05:40 0-Jan 07:20 0-Jan 08:20 0-Jan 08:30 0-Jan 8 F 06:55 0-Jan 06:45 0-Jan 07:05 0-Jan 08:05 0-Jan 08:15 0-Jan Sheet1

Hey all,

I have a spread sheet that I want to use data validation to only allow the user to enter a date in the format DD/MM/YYYY, if its not in that format then it displays the error message. the problem is that it will only let the user enter the format MM/DD/YYYY i tried to use the Custom section instead of the Date section but it still does the same. is there a way round it to get it to work?

Thanks

Tom

Hi All,

Could you please help me with this:
I have a spreadsheet with list on invoices which is populated daily with new records. I need to be sure that each invoice is posted only once (in order to avoid duplicates). I've tried to do this by using a validation list on the Invoice No column with the function =COUNTIF($B$2:$B$2800,B2)

dear camarades
i have a date cell that i implemented in a data validation
but i dnt want who is inputing the data (if he enter 15 March in A1) to enter 3 March in A2..
how this can be solved?

Okay i am fairly new to Access so bare with me. I have created a split database for my dept at work.

I have two tables in my front end that are linked to the actual tables in the back end: [QA Route] and [Focus Techs]

I have created a split form called "Daily Route" that was created from the data in [QA Route].

Here is my question:

I have a column in [QA Route] named Install Tech and i have a column in [Focus Techs] called Focus Tech. In my form (Daily Route) i want to place a validation rule in a column called Focus Tech to do the following:

Return a "Y" if the data in [QA Route]![Install Tech] matches with a value in [Focus Techs]![Focus Tech].

Return a "N" if the data in [QA Route]![Install Tech] does not find a match with a value in [Focus Techs]![Focus Tech].

I hope this makes sense. I have tried using the expression builder, but haven't gotten anything to work.

Thanks in advance.

Hi
The code below works very well before I used copy & paste to copy a value(17) into one of the cells in A1:C13. When I copy & paste, an unvalid number (12) can be pasted into a cell without any error message, let alone undoing the paste. And what's more, after this copy & paste, all cells accept any values input. How can I avoid this copy and paste ruinning my data validation? Thanks!

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1:C12")) Is Nothing Then
If Target.Value < 1 Or Target.Value > 12 Then
Application.EnableEvents = False
Target.Select: MsgBox "Invalid data": Target = ""
Application.EnableEvents = True
End If
End If
End Sub

HI,

I need to prevent the user from deleting the data in a particular column using Data validation formula for that range of cells for eg A1:A10

Thanks in Advance.

Hi there

Is there some way to force MS Excel 2003 to allow 4 rules to be entered under conditional formatting????

I wouldn't have thought so, but I thought it wouldn't hurt to ask!

I am creating a file which will be used on both 2007 and 2003 versions. Failing the above, I was thinking of setting up the conditional formatting with the 4 rules I want in the 2007 version, to see what happens when it is opened in 2003. Would it just drop the last rule?

Thanks
Sunil