That's ok, I probably didn't state the problem clearly.

I don't want to clear the key out, only any data the user may have entered
below it. It's kind of like a survey ... I have a list of subjects they can
pick from and they have to answer questions related to their selection.

In a nutshell, the "key" from the list needs to stay but any data in
specific cells within rows 12 to 500 have to be cleared out because when they
reselect another "key", then they need to start their answers over again.

Hope this makes sense.

"ilia" wrote:

Do you need to clear out the entire worksheet, or just the row where
the key was changed? The procedure above will clear out row 11, if
you change (or delete) the value in G11; likewise, it will clear out
row 10 if you change or delete G10.

I may have misunderstood your question.


On Oct 19, 11:53 am, PurpleMilk
wrote:
Thanks Ilia. Unless I'm doing something wrong, does this only work once?

Users can potentially reselect a key multiple times. For example, the first
time in G11 is blank and user selelcts "Answer A" and fills in all the
required info associated with Answer A on the worksheet.... then they realize
they should have picked "Answer B" instead and here is where I need to clear
the worksheet out.... then for whatever reason they decide to go back to
"Answer A" (again, need to clear out the worksheet).



"ilia" wrote:
Put this in the module of the worksheet where you have this list.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim currVal As Variant
Application.EnableEvents = False
If Not (Intersect(Target, Me.Range("G:G")) Is Nothing) Then
With Target
currVal = .Value
.EntireRow.Clear
.Value = currVal
End With
End If
Application.EnableEvents = True
End Sub

Notice, this makes Undo unavailable.

On Oct 19, 10:00 am, PurpleMilk
wrote:
In VBA, is there a way to tell when a key has changed?

For example, I have a list that users select from (in cell G10 - this is the
key), and under the list the users can enter some data associated with their
selection.

In the event they decide to go back and change their list selection (the
key), I would like to automatically clear out any input they already filled
so they can start again.

Any ideas?- Hide quoted text -

- Show quoted text -