Allow only one cell entry in a row

Hello!

I'm working on an Excel spreadsheet that is going to be used as a survey for the elderly to fill out. There are many rows and they look something like:

Please Login or Register  to view this content.

What I need to do is make it so that only one 'x' can be entered per row. That way, only one answer can be marked.

Any ideas on how to make this happen?

Here is one technique.

It seems like you want to limit the entries in columns B:G with only one entry per row. If this is the case, highlight the range that you want to apply the limitation. For example, if entries are being made from B2:G10, highlight B2:G10.

Next, go to Data in the main toolbar, then choose validation. In the drop down menu, choose custom, and enter this formula:

=COUNTA($B2:$G2)<=1

Now, each row can only accept one entry.

Let me know if that works.

That worked, but now I can't use my drop-down menu. The validation was set to be a list that allows for 'x' or blank. Is there a way to do this and still keep the drop-down menu?

Are you willing to use VBA to achieve your needs?

If so, you can use a Worksheet_change code to do what you need. Try adapting this:

Please Login or Register  to view this content.

Wha?

Sorry, I don't know anything about Visual Basic.

Could you attach a .zip sample of your workbook? Perhaps, we can adapt something to suit your needs.

Actually, I figured out where to paste it and got it to work.

Thank you!!!

Hi - I have a slight variation on Oriana's request that I am hoping someone can help me with. I have three sections of my worksheet that I would like this VBA code to work with. For example: columns H:Q are 'product model', columns R:W are 'product color', and columns X:AB are 'product add-ons'. Across one row I want my users to be able to only select one cell in each of the three groups (so they select one model, one color, and one add-on). I've got the VBA code provided above to work for the first section (columns H:Q) - but I can't figure out how to modify it so that it works on the following two sections. Below is what I put into VBA - does anyone have any suggestions for how I can modify it so all three run on the same sheet? Thank you!!!

Private Sub Worksheet_Change(ByVal Target As Range)
Dim RowNum As Integer
RowNum = Target.Row

If WorksheetFunction.CountA(Range("H" & RowNum & ":Q" & RowNum)) > 1 Then
Range("H" & RowNum & ":Q" & RowNum).Select
Selection.ClearContents
Range("H" & RowNum).Select
MsgBox "Please only make one model selection per row"
End If
End Sub

Private Sub Worksheet_Change2(ByVal Target As Range)
Dim RowNum As Integer
RowNum = Target.Row

If WorksheetFunction.CountA(Range("R" & RowNum & ":W" & RowNum)) > 1 Then
Range("R" & RowNum & ":W" & RowNum).Select
Selection.ClearContents
Range("R" & RowNum).Select
MsgBox "Please only make one color selection per row"
End If
End Sub

Private Sub Worksheet_Change3(ByVal Target As Range)
Dim RowNum As Integer
RowNum = Target.Row

If WorksheetFunction.CountA(Range("X" & RowNum & ":AB" & RowNum)) > 1 Then
Range("X" & RowNum & ":AB" & RowNum).Select
Selection.ClearContents
Range("X" & RowNum).Select
MsgBox "You may only select one build option per row"
End If
End Sub

I figured out how to run my three sections of code by moving all the variations into one sub function:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim RowNum As Integer
RowNum = Target.Row

If WorksheetFunction.CountA(Range("H" & RowNum & ":Q" & RowNum)) > 1 Then
Range("H" & RowNum & ":Q" & RowNum).Select
Selection.ClearContents
Range("H" & RowNum).Select
MsgBox "Please only make one model selection per row"
End If

If WorksheetFunction.CountA(Range("R" & RowNum & ":W" & RowNum)) > 1 Then
Range("R" & RowNum & ":W" & RowNum).Select
Selection.ClearContents
Range("R" & RowNum).Select
MsgBox "Please only make one color selection per row"
End If

If WorksheetFunction.CountA(Range("X" & RowNum & ":AB" & RowNum)) > 1 Then
Range("X" & RowNum & ":AB" & RowNum).Select
Selection.ClearContents
Range("X" & RowNum).Select
MsgBox "You may only select one build option per row"
End If
End Sub

HOWEVER - now when I try to add text anywhere else in the worksheet it flash runs through all three messages and then deletes all my column headers. Any ideas from here?