Validation Question - Input Data Can't Equal Other Data

nico

Board Regular
Joined
Apr 26, 2005
Messages
111
I'm making a Form from a Table and want the user to be able to select 4 choices from 4 combo boxes. Basically, they are 'ordering' 4 different machines in priority from 1st to 4th. So the 4 combo boxes are labeled as "1st Priority, 2nd Priority, 3rd Priority, and 4th Priority". So if the order of the machines inputted was Machine 3, Machine 2, Machine 1, Machine 4, then the user would input:

1st Priority: Machine 3
2nd Priority: Machine 2
3rd Priority: Machine 1
4th Priority: Machine 4

For the 4 combo boxes I have listed the 4 values: "Machine 1, Machine 2, Machine 3, Machine 4". But, I would like the user to only be allowed to select 1 Machine for each combo box. For example, I don't want them to be able to select Machine 1 more than once. Is there a way to input a validation rule in the Table that states something for combo box 1 like: "field 1 does not equal field 2 or 3 or 4" and then for combo box 2: "field 2 does not equal field 1 or 3 or 4", etc. Or if there was a way to 'gray out' an already selected option so the user couldn't select it twice. Thanks!
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Marbles

Well-known Member
Joined
Feb 7, 2005
Messages
623
It's a bit of an overkill, but you could try this in the After Update of your form and the fields themselves, with A,B,C and D being your 4 Priority Fields.

Option Compare Database

Private Sub A_AfterUpdate()
If Me.A = Me.B Or Me.A = Me.C Or Me.A = Me.D Then Me.A = "": MsgBox "No Duplicates", , "Help"
End Sub

Private Sub B_AfterUpdate()
If Me.B = Me.A Or Me.B = Me.C Or Me.B = Me.D Then Me.B = "": MsgBox "No Duplicates", , "Help"
End Sub

Private Sub C_AfterUpdate()
If Me.C = Me.A Or Me.C = Me.B Or Me.C = Me.D Then Me.C = "": MsgBox "No Duplicates", , "Help"
End Sub

Private Sub D_AfterUpdate()
If Me.D = Me.A Or Me.D = Me.B Or Me.D = Me.C Then Me.D = "": MsgBox "No Duplicates", , "Help"
End Sub

Private Sub Form_AfterUpdate()
If Me.A = Me.B Or Me.A = Me.C Or Me.A = Me.D Then Me.A = "" _
: If Me.B = Me.A Or Me.B = Me.C Or Me.B = Me.D Then Me.B = "" _
: If Me.C = Me.A Or Me.C = Me.B Or Me.C = Me.D Then Me.C = "" _
: If Me.D = Me.A Or Me.D = Me.B Or Me.D = Me.C Then Me.D = "": MsgBox "No Duplicates", , "Help"
End Sub
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,322
Messages
5,769,452
Members
425,548
Latest member
macjagger17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top