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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Marbles

Well-known Member
Joined
Feb 7, 2005
Messages
626
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
 
Upvote 0

Forum statistics

Threads
1,186,826
Messages
5,960,022
Members
438,458
Latest member
manojkumarat1999

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