|
only1
how to make user to fill in at most value of 1 inside
cell c3,c4,c5. This means if c3 is inserted 1, c4 n c5 will auto set to 0. Or if c4 is inserted 1, c3 n c5 will set to 0. Or if c5 is inserted 1, c3 n c4 will set to 0. thank |
only1
You have to use a Worksheet_Change event sub:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 3 Then Application.EnableEvents = False Select Case Target.Row Case 3 Range(Cells(4, Target.Column), Cells(5, Target.Column)).Value = 0 Case 4 Cells(3, Target.Column).Value = 0 Cells(5, Target.Column).Value = 0 Case 5 Range(Cells(3, Target.Column), Cells(4, Target.Column)).Value = 0 End Select Application.EnableEvents = True End If End Sub Apply Data validation on cells c3:c5 to limit the value to 1. Regards, Stefi €˛kyoshirou€¯ ezt Ć*rta: how to make user to fill in at most value of 1 inside cell c3,c4,c5. This means if c3 is inserted 1, c4 n c5 will auto set to 0. Or if c4 is inserted 1, c3 n c5 will set to 0. Or if c5 is inserted 1, c3 n c4 will set to 0. thank |
only1
Hi Stefi,
i trying to do this: user can only enter one of the cells of C3,C4 or C5. And the value can only be 1. Other 2 cell i have auto set to 0 value. I mean user can enter: c3: 1 c4: 0 c5: 0 or c3: 0 c4: 0 c5: 1 or c3: 0 c4: 1 c5: 0 "Stefi" wrote: You have to use a Worksheet_Change event sub: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 3 Then Application.EnableEvents = False Select Case Target.Row Case 3 Range(Cells(4, Target.Column), Cells(5, Target.Column)).Value = 0 Case 4 Cells(3, Target.Column).Value = 0 Cells(5, Target.Column).Value = 0 Case 5 Range(Cells(3, Target.Column), Cells(4, Target.Column)).Value = 0 End Select Application.EnableEvents = True End If End Sub Apply Data validation on cells c3:c5 to limit the value to 1. Regards, Stefi €˛kyoshirou€¯ ezt Ć*rta: how to make user to fill in at most value of 1 inside cell c3,c4,c5. This means if c3 is inserted 1, c4 n c5 will auto set to 0. Or if c4 is inserted 1, c3 n c5 will set to 0. Or if c5 is inserted 1, c3 n c4 will set to 0. thank |
only1
Hi Kyoshirou,
The sub I attached does exactly what you need: if a value is entered in any of cells C3,C4,C5, the other two will be automatically set to 0. Applying Data validation to these cells will allow entering by typing only value 1 to any of these cells. Post if you need further assistance for installing the event sub! Regards, Stefi €˛kyoshirou€¯ ezt Ć*rta: Hi Stefi, i trying to do this: user can only enter one of the cells of C3,C4 or C5. And the value can only be 1. Other 2 cell i have auto set to 0 value. I mean user can enter: c3: 1 c4: 0 c5: 0 or c3: 0 c4: 0 c5: 1 or c3: 0 c4: 1 c5: 0 "Stefi" wrote: You have to use a Worksheet_Change event sub: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 3 Then Application.EnableEvents = False Select Case Target.Row Case 3 Range(Cells(4, Target.Column), Cells(5, Target.Column)).Value = 0 Case 4 Cells(3, Target.Column).Value = 0 Cells(5, Target.Column).Value = 0 Case 5 Range(Cells(3, Target.Column), Cells(4, Target.Column)).Value = 0 End Select Application.EnableEvents = True End If End Sub Apply Data validation on cells c3:c5 to limit the value to 1. Regards, Stefi €˛kyoshirou€¯ ezt Ć*rta: how to make user to fill in at most value of 1 inside cell c3,c4,c5. This means if c3 is inserted 1, c4 n c5 will auto set to 0. Or if c4 is inserted 1, c3 n c5 will set to 0. Or if c5 is inserted 1, c3 n c4 will set to 0. thank |
only1
i have tried it, cant works.
Please help me with your install event. "Stefi" wrote: Hi Kyoshirou, The sub I attached does exactly what you need: if a value is entered in any of cells C3,C4,C5, the other two will be automatically set to 0. Applying Data validation to these cells will allow entering by typing only value 1 to any of these cells. Post if you need further assistance for installing the event sub! Regards, Stefi €˛kyoshirou€¯ ezt Ć*rta: Hi Stefi, i trying to do this: user can only enter one of the cells of C3,C4 or C5. And the value can only be 1. Other 2 cell i have auto set to 0 value. I mean user can enter: c3: 1 c4: 0 c5: 0 or c3: 0 c4: 0 c5: 1 or c3: 0 c4: 1 c5: 0 "Stefi" wrote: You have to use a Worksheet_Change event sub: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 3 Then Application.EnableEvents = False Select Case Target.Row Case 3 Range(Cells(4, Target.Column), Cells(5, Target.Column)).Value = 0 Case 4 Cells(3, Target.Column).Value = 0 Cells(5, Target.Column).Value = 0 Case 5 Range(Cells(3, Target.Column), Cells(4, Target.Column)).Value = 0 End Select Application.EnableEvents = True End If End Sub Apply Data validation on cells c3:c5 to limit the value to 1. Regards, Stefi €˛kyoshirou€¯ ezt Ć*rta: how to make user to fill in at most value of 1 inside cell c3,c4,c5. This means if c3 is inserted 1, c4 n c5 will auto set to 0. Or if c4 is inserted 1, c3 n c5 will set to 0. Or if c5 is inserted 1, c3 n c4 will set to 0. thank |
only1
Invoke VBA (Alt+F11)
In Project Explorer window (Ctrl+R) right-click on the sheet name containing your C3:C5 cells, choose View Code from the local menu Choose Worksheet from the Objects drop-down list (on left hand side) Copy the attached sub into the window below Stefi €˛kyoshirou€¯ ezt Ć*rta: i have tried it, cant works. Please help me with your install event. "Stefi" wrote: Hi Kyoshirou, The sub I attached does exactly what you need: if a value is entered in any of cells C3,C4,C5, the other two will be automatically set to 0. Applying Data validation to these cells will allow entering by typing only value 1 to any of these cells. Post if you need further assistance for installing the event sub! Regards, Stefi €˛kyoshirou€¯ ezt Ć*rta: Hi Stefi, i trying to do this: user can only enter one of the cells of C3,C4 or C5. And the value can only be 1. Other 2 cell i have auto set to 0 value. I mean user can enter: c3: 1 c4: 0 c5: 0 or c3: 0 c4: 0 c5: 1 or c3: 0 c4: 1 c5: 0 "Stefi" wrote: You have to use a Worksheet_Change event sub: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 3 Then Application.EnableEvents = False Select Case Target.Row Case 3 Range(Cells(4, Target.Column), Cells(5, Target.Column)).Value = 0 Case 4 Cells(3, Target.Column).Value = 0 Cells(5, Target.Column).Value = 0 Case 5 Range(Cells(3, Target.Column), Cells(4, Target.Column)).Value = 0 End Select Application.EnableEvents = True End If End Sub Apply Data validation on cells c3:c5 to limit the value to 1. Regards, Stefi €˛kyoshirou€¯ ezt Ć*rta: how to make user to fill in at most value of 1 inside cell c3,c4,c5. This means if c3 is inserted 1, c4 n c5 will auto set to 0. Or if c4 is inserted 1, c3 n c5 will set to 0. Or if c5 is inserted 1, c3 n c4 will set to 0. thank |
only1
right-click on worksheet tab, "view code", copy/paste.
"Stefi" wrote: Invoke VBA (Alt+F11) In Project Explorer window (Ctrl+R) right-click on the sheet name containing your C3:C5 cells, choose View Code from the local menu Choose Worksheet from the Objects drop-down list (on left hand side) Copy the attached sub into the window below Stefi €˛kyoshirou€¯ ezt Ć*rta: i have tried it, cant works. Please help me with your install event. "Stefi" wrote: Hi Kyoshirou, The sub I attached does exactly what you need: if a value is entered in any of cells C3,C4,C5, the other two will be automatically set to 0. Applying Data validation to these cells will allow entering by typing only value 1 to any of these cells. Post if you need further assistance for installing the event sub! Regards, Stefi €˛kyoshirou€¯ ezt Ć*rta: Hi Stefi, i trying to do this: user can only enter one of the cells of C3,C4 or C5. And the value can only be 1. Other 2 cell i have auto set to 0 value. I mean user can enter: c3: 1 c4: 0 c5: 0 or c3: 0 c4: 0 c5: 1 or c3: 0 c4: 1 c5: 0 "Stefi" wrote: You have to use a Worksheet_Change event sub: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 3 Then Application.EnableEvents = False Select Case Target.Row Case 3 Range(Cells(4, Target.Column), Cells(5, Target.Column)).Value = 0 Case 4 Cells(3, Target.Column).Value = 0 Cells(5, Target.Column).Value = 0 Case 5 Range(Cells(3, Target.Column), Cells(4, Target.Column)).Value = 0 End Select Application.EnableEvents = True End If End Sub Apply Data validation on cells c3:c5 to limit the value to 1. Regards, Stefi €˛kyoshirou€¯ ezt Ć*rta: how to make user to fill in at most value of 1 inside cell c3,c4,c5. This means if c3 is inserted 1, c4 n c5 will auto set to 0. Or if c4 is inserted 1, c3 n c5 will set to 0. Or if c5 is inserted 1, c3 n c4 will set to 0. thank |
only1
My MAIN sheet got some coding.. will it crash?
The code should not be inside Data sheet right? It cant works. Any advise? "Stefi" wrote: Invoke VBA (Alt+F11) In Project Explorer window (Ctrl+R) right-click on the sheet name containing your C3:C5 cells, choose View Code from the local menu Choose Worksheet from the Objects drop-down list (on left hand side) Copy the attached sub into the window below Stefi €˛kyoshirou€¯ ezt Ć*rta: i have tried it, cant works. Please help me with your install event. "Stefi" wrote: Hi Kyoshirou, The sub I attached does exactly what you need: if a value is entered in any of cells C3,C4,C5, the other two will be automatically set to 0. Applying Data validation to these cells will allow entering by typing only value 1 to any of these cells. Post if you need further assistance for installing the event sub! Regards, Stefi €˛kyoshirou€¯ ezt Ć*rta: Hi Stefi, i trying to do this: user can only enter one of the cells of C3,C4 or C5. And the value can only be 1. Other 2 cell i have auto set to 0 value. I mean user can enter: c3: 1 c4: 0 c5: 0 or c3: 0 c4: 0 c5: 1 or c3: 0 c4: 1 c5: 0 "Stefi" wrote: You have to use a Worksheet_Change event sub: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 3 Then Application.EnableEvents = False Select Case Target.Row Case 3 Range(Cells(4, Target.Column), Cells(5, Target.Column)).Value = 0 Case 4 Cells(3, Target.Column).Value = 0 Cells(5, Target.Column).Value = 0 Case 5 Range(Cells(3, Target.Column), Cells(4, Target.Column)).Value = 0 End Select Application.EnableEvents = True End If End Sub Apply Data validation on cells c3:c5 to limit the value to 1. Regards, Stefi €˛kyoshirou€¯ ezt Ć*rta: how to make user to fill in at most value of 1 inside cell c3,c4,c5. This means if c3 is inserted 1, c4 n c5 will auto set to 0. Or if c4 is inserted 1, c3 n c5 will set to 0. Or if c5 is inserted 1, c3 n c4 will set to 0. thank |
only1
See e-mail I sent you!!!
"kyoshirou" wrote: My MAIN sheet got some coding.. will it crash? The code should not be inside Data sheet right? It cant works. Any advise? "Stefi" wrote: Invoke VBA (Alt+F11) In Project Explorer window (Ctrl+R) right-click on the sheet name containing your C3:C5 cells, choose View Code from the local menu Choose Worksheet from the Objects drop-down list (on left hand side) Copy the attached sub into the window below Stefi €˛kyoshirou€¯ ezt Ć*rta: i have tried it, cant works. Please help me with your install event. "Stefi" wrote: Hi Kyoshirou, The sub I attached does exactly what you need: if a value is entered in any of cells C3,C4,C5, the other two will be automatically set to 0. Applying Data validation to these cells will allow entering by typing only value 1 to any of these cells. Post if you need further assistance for installing the event sub! Regards, Stefi €˛kyoshirou€¯ ezt Ć*rta: Hi Stefi, i trying to do this: user can only enter one of the cells of C3,C4 or C5. And the value can only be 1. Other 2 cell i have auto set to 0 value. I mean user can enter: c3: 1 c4: 0 c5: 0 or c3: 0 c4: 0 c5: 1 or c3: 0 c4: 1 c5: 0 "Stefi" wrote: You have to use a Worksheet_Change event sub: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 3 Then Application.EnableEvents = False Select Case Target.Row Case 3 Range(Cells(4, Target.Column), Cells(5, Target.Column)).Value = 0 Case 4 Cells(3, Target.Column).Value = 0 Cells(5, Target.Column).Value = 0 Case 5 Range(Cells(3, Target.Column), Cells(4, Target.Column)).Value = 0 End Select Application.EnableEvents = True End If End Sub Apply Data validation on cells c3:c5 to limit the value to 1. Regards, Stefi €˛kyoshirou€¯ ezt Ć*rta: how to make user to fill in at most value of 1 inside cell c3,c4,c5. This means if c3 is inserted 1, c4 n c5 will auto set to 0. Or if c4 is inserted 1, c3 n c5 will set to 0. Or if c5 is inserted 1, c3 n c4 will set to 0. thank |
only1
Oh.. i know already. THanks!
But then now the default is 0. Can i set it to empty? Only when users start to touch the cells, then it will become 0? "Stefi" wrote: You have to use a Worksheet_Change event sub: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 3 Then Application.EnableEvents = False Select Case Target.Row Case 3 Range(Cells(4, Target.Column), Cells(5, Target.Column)).Value = 0 Case 4 Cells(3, Target.Column).Value = 0 Cells(5, Target.Column).Value = 0 Case 5 Range(Cells(3, Target.Column), Cells(4, Target.Column)).Value = 0 End Select Application.EnableEvents = True End If End Sub Apply Data validation on cells c3:c5 to limit the value to 1. Regards, Stefi €˛kyoshirou€¯ ezt Ć*rta: how to make user to fill in at most value of 1 inside cell c3,c4,c5. This means if c3 is inserted 1, c4 n c5 will auto set to 0. Or if c4 is inserted 1, c3 n c5 will set to 0. Or if c5 is inserted 1, c3 n c4 will set to 0. thank |
only1
When do you want to set the value of these cells to default 0? When opening
the workbook? If so, you have to apply a Workbook_Open event sub, like: Private Sub Workbook_Open() Range("C3:C5").Value = 0 End Sub Invoke VBA (Alt+F11) In Project Explorer window (Ctrl+R) right-click on the workbook name, choose View Code from the local menu Choose Workbook from the Objects drop-down list (on left hand side) Copy the attached sub into the window below Regards, Stefi €˛kyoshirou€¯ ezt Ć*rta: Oh.. i know already. THanks! But then now the default is 0. Can i set it to empty? Only when users start to touch the cells, then it will become 0? "Stefi" wrote: You have to use a Worksheet_Change event sub: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 3 Then Application.EnableEvents = False Select Case Target.Row Case 3 Range(Cells(4, Target.Column), Cells(5, Target.Column)).Value = 0 Case 4 Cells(3, Target.Column).Value = 0 Cells(5, Target.Column).Value = 0 Case 5 Range(Cells(3, Target.Column), Cells(4, Target.Column)).Value = 0 End Select Application.EnableEvents = True End If End Sub Apply Data validation on cells c3:c5 to limit the value to 1. Regards, Stefi €˛kyoshirou€¯ ezt Ć*rta: how to make user to fill in at most value of 1 inside cell c3,c4,c5. This means if c3 is inserted 1, c4 n c5 will auto set to 0. Or if c4 is inserted 1, c3 n c5 will set to 0. Or if c5 is inserted 1, c3 n c4 will set to 0. thank |
only1
Hi Stefi,
I would like the cells to appear everthing empty, only after the user start to enter one of the 3 cells. Then, the cells will become to 0. (When it is not 1). Thanks again! "Stefi" wrote: When do you want to set the value of these cells to default 0? When opening the workbook? If so, you have to apply a Workbook_Open event sub, like: Private Sub Workbook_Open() Range("C3:C5").Value = 0 End Sub Invoke VBA (Alt+F11) In Project Explorer window (Ctrl+R) right-click on the workbook name, choose View Code from the local menu Choose Workbook from the Objects drop-down list (on left hand side) Copy the attached sub into the window below Regards, Stefi €˛kyoshirou€¯ ezt Ć*rta: Oh.. i know already. THanks! But then now the default is 0. Can i set it to empty? Only when users start to touch the cells, then it will become 0? "Stefi" wrote: You have to use a Worksheet_Change event sub: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 3 Then Application.EnableEvents = False Select Case Target.Row Case 3 Range(Cells(4, Target.Column), Cells(5, Target.Column)).Value = 0 Case 4 Cells(3, Target.Column).Value = 0 Cells(5, Target.Column).Value = 0 Case 5 Range(Cells(3, Target.Column), Cells(4, Target.Column)).Value = 0 End Select Application.EnableEvents = True End If End Sub Apply Data validation on cells c3:c5 to limit the value to 1. Regards, Stefi €˛kyoshirou€¯ ezt Ć*rta: how to make user to fill in at most value of 1 inside cell c3,c4,c5. This means if c3 is inserted 1, c4 n c5 will auto set to 0. Or if c4 is inserted 1, c3 n c5 will set to 0. Or if c5 is inserted 1, c3 n c4 will set to 0. thank |
only1
Kyoshirou,
I do not really understand your problem. The sub does exactly what you wrote. The cells are entirely empty as default until the user enters a value in one of them. Then the value of the other two cells changes to 0. Please clarify your request, if you need something else. Stefi €˛kyoshirou€¯ ezt Ć*rta: Hi Stefi, I would like the cells to appear everthing empty, only after the user start to enter one of the 3 cells. Then, the cells will become to 0. (When it is not 1). Thanks again! "Stefi" wrote: When do you want to set the value of these cells to default 0? When opening the workbook? If so, you have to apply a Workbook_Open event sub, like: Private Sub Workbook_Open() Range("C3:C5").Value = 0 End Sub Invoke VBA (Alt+F11) In Project Explorer window (Ctrl+R) right-click on the workbook name, choose View Code from the local menu Choose Workbook from the Objects drop-down list (on left hand side) Copy the attached sub into the window below Regards, Stefi €˛kyoshirou€¯ ezt Ć*rta: Oh.. i know already. THanks! But then now the default is 0. Can i set it to empty? Only when users start to touch the cells, then it will become 0? "Stefi" wrote: You have to use a Worksheet_Change event sub: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 3 Then Application.EnableEvents = False Select Case Target.Row Case 3 Range(Cells(4, Target.Column), Cells(5, Target.Column)).Value = 0 Case 4 Cells(3, Target.Column).Value = 0 Cells(5, Target.Column).Value = 0 Case 5 Range(Cells(3, Target.Column), Cells(4, Target.Column)).Value = 0 End Select Application.EnableEvents = True End If End Sub Apply Data validation on cells c3:c5 to limit the value to 1. Regards, Stefi €˛kyoshirou€¯ ezt Ć*rta: how to make user to fill in at most value of 1 inside cell c3,c4,c5. This means if c3 is inserted 1, c4 n c5 will auto set to 0. Or if c4 is inserted 1, c3 n c5 will set to 0. Or if c5 is inserted 1, c3 n c4 will set to 0. thank |
only1
Thanks Stefi, i go check again.
Sry Toppers, my mail server is down. Have to check later. Thanks both! "Stefi" wrote: Kyoshirou, I do not really understand your problem. The sub does exactly what you wrote. The cells are entirely empty as default until the user enters a value in one of them. Then the value of the other two cells changes to 0. Please clarify your request, if you need something else. Stefi €˛kyoshirou€¯ ezt Ć*rta: Hi Stefi, I would like the cells to appear everthing empty, only after the user start to enter one of the 3 cells. Then, the cells will become to 0. (When it is not 1). Thanks again! "Stefi" wrote: When do you want to set the value of these cells to default 0? When opening the workbook? If so, you have to apply a Workbook_Open event sub, like: Private Sub Workbook_Open() Range("C3:C5").Value = 0 End Sub Invoke VBA (Alt+F11) In Project Explorer window (Ctrl+R) right-click on the workbook name, choose View Code from the local menu Choose Workbook from the Objects drop-down list (on left hand side) Copy the attached sub into the window below Regards, Stefi €˛kyoshirou€¯ ezt Ć*rta: Oh.. i know already. THanks! But then now the default is 0. Can i set it to empty? Only when users start to touch the cells, then it will become 0? "Stefi" wrote: You have to use a Worksheet_Change event sub: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 3 Then Application.EnableEvents = False Select Case Target.Row Case 3 Range(Cells(4, Target.Column), Cells(5, Target.Column)).Value = 0 Case 4 Cells(3, Target.Column).Value = 0 Cells(5, Target.Column).Value = 0 Case 5 Range(Cells(3, Target.Column), Cells(4, Target.Column)).Value = 0 End Select Application.EnableEvents = True End If End Sub Apply Data validation on cells c3:c5 to limit the value to 1. Regards, Stefi €˛kyoshirou€¯ ezt Ć*rta: how to make user to fill in at most value of 1 inside cell c3,c4,c5. This means if c3 is inserted 1, c4 n c5 will auto set to 0. Or if c4 is inserted 1, c3 n c5 will set to 0. Or if c5 is inserted 1, c3 n c4 will set to 0. thank |
| All times are GMT +1. The time now is 10:18 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com