how to limit row input to only 1 cell

[Mel]

I have a spreadsheet with cells B4 to AC4 down to AC78. Each row has
28 fields that the user will either indicate a "1" or a "2" only once
on each row. ie. Row 4 could have cell E4 with a "1" row 5 G5 with
a "2" and so on.

I would like to be able to protect each row so that only once cell is
completed. ie. only "1" is filled in once, not twice.

I would like a message box to pop-up to state "Only one selection
allowed".

I started to work on the script but have not been sucessful.

thanks.

Mel

 

[Rick Rothstein]

Give the following event code a try...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B4:AC78")) Is Nothing Then
With Application
If (.CountIf(Target.EntireRow, "1") + _
.CountIf(Target.EntireRow, "2")) > 1 Then
MsgBox "Sorry, only one ""1"" or ""2"" per row!"
.EnableEvents = False
Target.Value = ""
.EnableEvents = True
End If
End With
End If
End Sub

To install it, right click the tab at the bottom of the worksheet that is to
have this functionality, select View Code from the popup menu that appears
and then copy/paste the above into the code window that appeared. Now, go
back to the worksheet and try to enter more than one 1 or 2 in a single row
within the range B4:AC78. Note, because your question didn't address the
issue, the above code will permit other text to be typed in the cells... it
only checks for multiple instances of "1" and/or "2" and nothing else.

 

[Mel]

Give the following event code a try...

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("B4:AC78")) Is Nothing Then
    With Application
      If (.CountIf(Target.EntireRow, "1") + _
          .CountIf(Target.EntireRow, "2")) > 1 Then
        MsgBox "Sorry, only one ""1"" or ""2"" per row!"
        .EnableEvents = False
        Target.Value = ""
        .EnableEvents = True
      End If
    End With
  End If
End Sub

To install it, right click the tab at the bottom of the worksheet that isto
have this functionality, select View Code from the popup menu that appears
and then copy/paste the above into the code window that appeared. Now, go
back to the worksheet and try to enter more than one 1 or 2 in a single row
within the range B4:AC78. Note, because your question didn't address the
issue, the above code will permit other text to be typed in the cells... it
only checks for multiple instances of "1" and/or "2" and nothing else.

--
Rick (MVP - Excel)









- Show quoted text -

This almost works. After I added the macro, if I want to change a
cell previously completed, it will not let me. I can delete the
1 or 2 but then cannot add to any other cell. This would be needed if
someone completed a cell in error. They would need to delete
then add to correct cell.

thx

 

[Rick Rothstein]

I may not be understanding your original requirements then. As far as I can
tell, my code will always generate an error in the following cases... if a
row already has a 1 **or** a 2 on it, then the user is not allowed to type
**either** a 1 or 2 on that line. I don't see what error they can be
correcting that should be allowed if there is already a 1 or 2 on the row.

--
Rick (MVP - Excel)

Give the following event code a try...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B4:AC78")) Is Nothing Then
With Application
If (.CountIf(Target.EntireRow, "1") + _
.CountIf(Target.EntireRow, "2")) > 1 Then
MsgBox "Sorry, only one ""1"" or ""2"" per row!"
.EnableEvents = False
Target.Value = ""
.EnableEvents = True
End If
End With
End If
End Sub

To install it, right click the tab at the bottom of the worksheet that is
to
have this functionality, select View Code from the popup menu that appears
and then copy/paste the above into the code window that appeared. Now, go
back to the worksheet and try to enter more than one 1 or 2 in a single
row
within the range B4:AC78. Note, because your question didn't address the
issue, the above code will permit other text to be typed in the cells...
it
only checks for multiple instances of "1" and/or "2" and nothing else.

--
Rick (MVP - Excel)









- Show quoted text -

This almost works. After I added the macro, if I want to change a
cell previously completed, it will not let me. I can delete the
1 or 2 but then cannot add to any other cell. This would be needed if
someone completed a cell in error. They would need to delete
then add to correct cell.

thx