On Exit Command

  • Thread starter Thread starter Ben
  • Start date Start date
B

Ben

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)
 
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
 
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)
 
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)




.
Click to expand...
 
Back
Top