I would like to use a "key-up" like event to clear certain cells in a
worksheet when someone enters a new input value in another (unrelated) cell.

I know how to program the event I want in terms of VB, but how do I
create/capture an event tied to a single cell on a worksheet??

Maybe it's brain flatulence, but I'm at a dead end here (tired).

--


Frank Bachman
(Grumpy Aero Guy)

You can find the events available for a worksheet in the object browser.

I believe in this case your code belongs in the WorksheetChange event. That is
triggered when the user changes ANY cell, so you must check for the cell of
interest. Notice also that you have to turn event trapping off before you
clear cells.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$4" Then
Application.EnableEvents = False
Me.Cells(12, 11).Resize(3, 5).Clear
Application.EnableEvents = True
End If
End Sub

On Sun, 6 Mar 2005 18:12:51 -0500, "Grumpy Aero Guy"
wrote:

I would like to use a "key-up" like event to clear certain cells in a
worksheet when someone enters a new input value in another (unrelated) cell.

I know how to program the event I want in terms of VB, but how do I
create/capture an event tied to a single cell on a worksheet??

Maybe it's brain flatulence, but I'm at a dead end here (tired).

There is a worksheet_change event that you can tie into.

Rightclick on the worksheet tab that you want to have this behavior. Select
View Code and paste this into the code window you see.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("a1")) Is Nothing Then Exit Sub

Application.EnableEvents = False
'whatever your range is
Me.Range("b3:c9").Clear
Application.EnableEvents = True
End Sub

Adjust the ranges according to your specs. I looked for any changed in A1 and
cleared the values/formulas in B3:C9.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

You can read more about events at:
Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm


Grumpy Aero Guy wrote:

I would like to use a "key-up" like event to clear certain cells in a
worksheet when someone enters a new input value in another (unrelated) cell.

I know how to program the event I want in terms of VB, but how do I
create/capture an event tied to a single cell on a worksheet??

Maybe it's brain flatulence, but I'm at a dead end here (tired).

--

Frank Bachman
(Grumpy Aero Guy)

--

Dave Peterson

THANK YOU FOLKS VERY MUCH !

--


Frank Bachman
(Grumpy Aero Guy)


"Grumpy Aero Guy" wrote in message
...
I would like to use a "key-up" like event to clear certain cells in a
worksheet when someone enters a new input value in another (unrelated)
cell.

I know how to program the event I want in terms of VB, but how do I
create/capture an event tied to a single cell on a worksheet??

Maybe it's brain flatulence, but I'm at a dead end here (tired).

--


Frank Bachman
(Grumpy Aero Guy)