Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to limit row input to only 1 cell
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to limit row input to only 1 cell
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) "Mel" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to limit row input to only 1 cell
On Oct 16, 1:57*pm, "Rick Rothstein"
wrote: 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) "Mel" wrote in message ... 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- Hide quoted text - - 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to limit row input to only 1 cell
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) "Mel" wrote in message ... On Oct 16, 1:57 pm, "Rick Rothstein" wrote: 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) "Mel" wrote in message ... 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- Hide quoted text - - 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
limit cell input | Excel Programming | |||
Limit the input in a cell to a single character | Excel Programming | |||
Limit characters in an Input Box | Excel Programming | |||
Limit input in a textbox | Excel Programming | |||
Limit character input | Excel Programming |