How to create a data validation rule that includes a drop down menu and forbids the user from editing a cell if one other cell in that row is edited?

[Eggy66]

Say I have this table:

How can I make it so that the user are only able to mark one column for each row while retaining the drop down menu?
Any help would be much appreciated!

 

[awoohaw]

Do you want a VBA solution?

 

[kevin9999]

Please try the following on a copy of your workbook:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("B:E"), Target) Is Nothing Then
       Application.EnableEvents = False
       Dim i As Long
       With Range(Cells(Target.Row, 2), Cells(Target.Row, 5))
           i = WorksheetFunction.CountIf(.Cells, "x")
           If i > 1 Then
               MsgBox "Only one category allowed per word"
               Target = ""
           End If
       End With
       Application.EnableEvents = True
    End If
End Sub

 

[awoohaw]

Here is another variant. This just deletes the other cells in row without any other error checking:

During set up you just need to input the row and column numbers that are the top and bottom limits of the range.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
TargetAdd = Target.Address
Call ClearOtherCells(TargetAdd)
Application.EnableEvents = True
End Sub


Sub ClearOtherCells(TargetAddress As Variant)
Dim ColumnChanged As Integer
Dim RowChanged As Integer
Dim ColumnOffset As Integer
Dim i As Integer

RowChanged = Range(TargetAddress).Row
ColumnChanged = Range(TargetAddress).Column
' This ensures only B2:E5 are processed
If (RowChanged >= 2 And RowChanged <= 5 And ColumnChanged >= 2 And ColumnChanged <= 5) = False Then Exit Sub
'Loop for number of columns
For i = 2 To 5
ColumnOffset = i - ColumnChanged
      If ColumnOffset <> 0 Then
        Range(TargetAddress).Offset(0, ColumnOffset).ClearContents
        End If
 Next i
End Sub