Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Restricting Cell Input (2000/2003)

    I have a worksheet where I want the user to input an x in a cell, but only one cell on a row.
    If the cells are b4,c4, and d4, -and the user places an x in any one of these cells. can the other two cells be made unavailable for entry.
    At the beginning all 3 cells are available for entry. If an "x " is originally placed in b4-then c4 and d4 wouldn't allow an entry. If an x is originally placed in c4-then b4 and d4 wouldn't allow an entry
    the same for an entry in d4 ---b4 and c4 wouldn't allow an entry.
    Any ideas or help would be appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Restricting Cell Input (2000/2003)

    Select B44
    Select Data | Validation.
    Select Custom from the Allow dropdown list.
    Enter the following formula in the box:

    =($B$4&$C$4&$D$4="x")

    Clear the check box "Ignore blank".
    Activate the 'Error message' tab.
    Make sure that Stop is selected as style, and enter a suitable error message.
    You can also enter an instructive text in the 'Input message' tab if you wish.
    Click OK.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Restricting Cell Input (2000/2003)

    That did it. I was thinking data validation but I would have never got that figured out thanks so much.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Restricting Cell Input (2000/2003)

    If you would like the user to be able to change his mind after typing an X in one of cells by typing an X in one of the other cells and having the previous X cleared, the code below entered into the event module behind the worksheet will do this.

    <code>
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B44")) Is Nothing Then
    Application.EnableEvents = False
    If Not Intersect(Target, Range("B4")) Is Nothing Then
    If (UCase(Range("B4").Value) = "X") Or (Range("B4").Value = "") Then
    Range("B4").Value = UCase(Range("B4").Value)
    Range("C4").Value = ""
    Range("D4").Value = ""
    Else
    Range("B4").Value = ""
    Range("B4").Select
    MsgBox "Cell B4 can only contain an X"
    End If
    End If
    If Not Intersect(Target, Range("C4")) Is Nothing Then
    If (UCase(Range("C4").Value) = "X") Or (Range("C4").Value = "") Then
    Range("B4").Value = ""
    Range("C4").Value = UCase(Range("C4").Value)
    Range("D4").Value = ""
    Else
    Range("C4").Value = ""
    Range("C4").Select
    MsgBox "Cell C4 can only contain an X"
    End If
    End If
    If Not Intersect(Target, Range("D4")) Is Nothing Then
    If (UCase(Range("D4").Value) = "X") Or (Range("D4").Value = "") Then
    Range("B4").Value = ""
    Range("C4").Value = ""
    Range("D4").Value = UCase(Range("D4").Value)
    Else
    Range("D4").Value = ""
    Range("D4").Select
    MsgBox "Cell D4 can only contain an X"
    End If
    End If
    Application.EnableEvents = True
    End If
    End Sub
    </code>

    The attached workbook demonstrates the code.
    Attached Files Attached Files
    Legare Coleman

  5. #5
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Restricting Cell Input (2000/2003)

    Thanks this works good also.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •