Forcing data value in a cell
 

Hello
I have a question. I have a spreadsheet with three columns. Only one of the
columns must contain 1 and other two will be then 0. I need to automate the
following tast:

If I input 1 in one cell (of any of the three columns), then others two
cells should automatically change their values to 0.

Any help in this matter will be appreciated.
Thanks.

Trying2Learn

Forcing data value in a cell
 

Try this event routine (it works with columns A,B,C):

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
forcevalue = IIf(Target.Value = 1, 0, 1)
c = Target.Column
r = Target.Row
Select Case c
Case 1
Cells(r, 2).Value = forcevalue
Cells(r, 3).Value = forcevalue
Case 2
Cells(r, 1).Value = forcevalue
Cells(r, 3).Value = forcevalue
Case 3
Cells(r, 1).Value = forcevalue
Cells(r, 2).Value = forcevalue
End Select
Application.EnableEvents = True
End Sub

Warning: this macro assumes that you enter only 0 or 1.

Regards,
Stefi


€žTrying2Learn€� ezt Ã*rta:

Hello
I have a question. I have a spreadsheet with three columns. Only one of the
columns must contain 1 and other two will be then 0. I need to automate the
following tast:

If I input 1 in one cell (of any of the three columns), then others two
cells should automatically change their values to 0.

Any help in this matter will be appreciated.
Thanks.

Trying2Learn

Forcing data value in a cell
 

Hello Stefi,

Thanks a lot for your help.

Trying2Learn

"Stefi" wrote:

Try this event routine (it works with columns A,B,C):

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
forcevalue = IIf(Target.Value = 1, 0, 1)
c = Target.Column
r = Target.Row
Select Case c
Case 1
Cells(r, 2).Value = forcevalue
Cells(r, 3).Value = forcevalue
Case 2
Cells(r, 1).Value = forcevalue
Cells(r, 3).Value = forcevalue
Case 3
Cells(r, 1).Value = forcevalue
Cells(r, 2).Value = forcevalue
End Select
Application.EnableEvents = True
End Sub

Warning: this macro assumes that you enter only 0 or 1.

Regards,
Stefi


€žTrying2Learn€� ezt Ã*rta:

Hello
I have a question. I have a spreadsheet with three columns. Only one of the
columns must contain 1 and other two will be then 0. I need to automate the
following tast:

If I input 1 in one cell (of any of the three columns), then others two
cells should automatically change their values to 0.

Any help in this matter will be appreciated.
Thanks.

Trying2Learn