How to restrict user to make wrong selection in Drop down list?

[mso3]

Hi friends,

I have created a dependent drop down list for State, District and block.

If a user selected a drop down list then it is possible to change the name of State or District by other user resulting a wrong State, District and Block selection. I don't want to allow any user to change such wrong indirect selection.

For example:
If user select State 'B'; then the district should be either 'R, S, T'.
Here it is possible to change the State 'B' to either 'A, C, D, E' which is wrong after the selection by first user.
It's also possible to change the District after selection by first user.

I want to restrict the user to change dependent list. If the user wants to change the drop down list then he must have to change the dependent list.
In short ...
If he changed the State then he must change District and Block.
If he changed the District then he must change Block.

Any help will be highly appreciated.

Thanking you,

[tim201110]

maybe check for correspondence between j6,k6 & l6 before further calculations&?

[mso3]

Yes,

Thank you.

[JohnTopley]

Insert the code below in tab "Personal":

Right-click tab, select "view code" and paste code.

If State is changed, then District is set to blank and if District is changed Block is set to blank

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Column = 10 Or Target.Column = 11 Then
  If Target.Validation.Type = 3 Then
   Application.EnableEvents = False
   Target.Offset(0, 1).ClearContents
  End If
End If

exitHandler:
  Application.EnableEvents = True
  Exit Sub

End Sub

See http://www.contextures.com/xlDataVal02.html#clear

Change 10 and 11 in code for your actual columns

[mso3]

Hi John,

Thank you. It's very close to my requirement. I have made a little amendment in it to clear 2 columns to the right if the user edit State.

Requirement:

If the user edit State clear 2 columns to right. That is District and Block.
If the user edit District then clear 1 column to left and 1 column to right. That is State and Block.
If the user edit block then clear 2 columns to left. That is State and District.

In short I want to select the drop down list from column 'J' to 'L' direction only. User can not edit the drop down list from District or Block.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Column = 10 Or Target.Column = 11 Then
  If Target.Validation.Type = 3 Then
   Application.EnableEvents = False
   Target.Offset(0, 2).ClearContents
  End If
End If

exitHandler:
  Application.EnableEvents = True
  Exit Sub

End Sub

Please tell me what is the meaning of

.Validation.Type = 3 Then

Thank you.

[JohnTopley]

You cannot clear State/District if you enter Block because entry of Block is treated as a Change to it will clear State/District.

As block SHOULD be dependent on State/District it makes no sense to clear these.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Validation.Type = 3 Then
   Application.EnableEvents = False
   If Target.Column = 10 Then
   Target.Offset(0, 1).ClearContents
   Target.Offset(0, 2).ClearContents
   Else
       If Target.Column = 11 Then
       Target.Offset(0, 1).ClearContents
       End If
  End If
End If

exitHandler:
  Application.EnableEvents = True
  Exit Sub

End Sub

If Target.Validation.Type = 3 =DATA VALIDATION

[mso3]

Hi John,

Thank you.

Have a nice time.