ExcelBanter - On Exit Command

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - On Exit Command (https://www.excelbanter.com/excel-programming/287729-exit-command.html)

How do I set it a cell so that when a certain cell's
content has been changed other cells contents change.

For example I have 20 cells that either have a 'y'
or 'n'. These 'y', or 'n' values are input manually.

There is also one cell that contains a number that is
input manually.

I would like to set it so that the 20 cell's that contain
an 'y' or 'n' value are set to 'n' when the cell that has
a number in it is changed and only when it changes.

If this is not possible how about doing the same thing
when the cell is exited?

Thanks Ben

Ben,

This worksheet event code will do it

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("A1")) Is Nothing Then
Range("myRange").Value = "n"
End If

ws_exit:

Application.EnableEvents = True
End Sub

This goes in the worksheet cfode module. You also need to define a named
ranmge called 'myRange' to cover the cells you want to change

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Ben" wrote in message
...
How do I set it a cell so that when a certain cell's
content has been changed other cells contents change.

For example I have 20 cells that either have a 'y'
or 'n'. These 'y', or 'n' values are input manually.

There is also one cell that contains a number that is
input manually.

I would like to set it so that the 20 cell's that contain
an 'y' or 'n' value are set to 'n' when the cell that has
a number in it is changed and only when it changes.

If this is not possible how about doing the same thing
when the cell is exited?

Thanks Ben

Bob,

Thanks! How do I define the ranmge?

------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("D1")) Is Nothing Then
Range("D3:D26").Value = "n"
End If

ws_exit:

Application.EnableEvents = True
End Sub

-----Original Message-----
Ben,

This worksheet event code will do it

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("A1")) Is Nothing Then
Range("myRange").Value = "n"
End If

ws_exit:

Application.EnableEvents = True
End Sub

This goes in the worksheet cfode module. You also need
to define a named
ranmge called 'myRange' to cover the cells you want to
change

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Ben" wrote in
message
...
How do I set it a cell so that when a certain cell's
content has been changed other cells contents change.

For example I have 20 cells that either have a 'y'
or 'n'. These 'y', or 'n' values are input manually.

There is also one cell that contains a number that is
input manually.

I would like to set it so that the 20 cell's that
contain
an 'y' or 'n' value are set to 'n' when the cell that
has
a number in it is changed and only when it changes.

If this is not possible how about doing the same thing
when the cell is exited?

Thanks Ben

.

Select all of the cells to be in the range
Type the name in the Names box (the little textbox above the spreadsheet,
below the toolbars, and to the left of the formula bar)

Or if all the cells are contiguous, you can specify the range just as you
have done in your example.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

wrote in message
...
Bob,

Thanks! How do I define the ranmge?

------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("D1")) Is Nothing Then
Range("D3:D26").Value = "n"
End If

ws_exit:

Application.EnableEvents = True
End Sub

-----Original Message-----
Ben,

This worksheet event code will do it

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("A1")) Is Nothing Then
Range("myRange").Value = "n"
End If

ws_exit:

Application.EnableEvents = True
End Sub

This goes in the worksheet cfode module. You also need
to define a named
ranmge called 'myRange' to cover the cells you want to
change

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Ben" wrote in
message
...
How do I set it a cell so that when a certain cell's
content has been changed other cells contents change.

For example I have 20 cells that either have a 'y'
or 'n'. These 'y', or 'n' values are input manually.

There is also one cell that contains a number that is
input manually.

I would like to set it so that the 20 cell's that
contain
an 'y' or 'n' value are set to 'n' when the cell that
has
a number in it is changed and only when it changes.

If this is not possible how about doing the same thing
when the cell is exited?

Thanks Ben

.

Bob,

Thank you that works Perfectly!!

Ben
-----Original Message-----
Select all of the cells to be in the range
Type the name in the Names box (the little textbox above
the spreadsheet,
below the toolbars, and to the left of the formula bar)

Or if all the cells are contiguous, you can specify the
range just as you
have done in your example.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

wrote in message
...
Bob,

Thanks! How do I define the ranmge?

------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("D1")) Is Nothing
Then
Range("D3:D26").Value = "n"
End If

ws_exit:

Application.EnableEvents = True
End Sub

-----Original Message-----
Ben,

This worksheet event code will do it

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("A1")) Is Nothing
Then
Range("myRange").Value = "n"
End If

ws_exit:

Application.EnableEvents = True
End Sub

This goes in the worksheet cfode module. You also need
to define a named
ranmge called 'myRange' to cover the cells you want to
change

--

HTH

Bob Phillips
... looking out across Poole Harbour to the
Purbecks
(remove nothere from the email address if mailing
direct)

"Ben" wrote in
message
...
How do I set it a cell so that when a certain cell's
content has been changed other cells contents
change.

For example I have 20 cells that either have a 'y'
or 'n'. These 'y', or 'n' values are input
manually.

There is also one cell that contains a number that
is
input manually.

I would like to set it so that the 20 cell's that
contain
an 'y' or 'n' value are set to 'n' when the cell
that
has
a number in it is changed and only when it changes.

If this is not possible how about doing the same
thing
when the cell is exited?

Thanks Ben

.

.