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?

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?

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?

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 -

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 -

In which case, your key is always in row 11? Then try this one
instead (again, in the worksheet's module):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim keyRowRange As Range
Dim rng As Range

Const firstRowToClear As Long = 12
Const lastRowToClear As Long = 500

Application.EnableEvents = False

Set keyRowRange = Me.Range("11:11")

If Not (Intersect(Target, keyRowRange) Is Nothing) Then
For Each rng In Target
Me.Range(Me.Cells(firstRowToClear, rng.Column), _
Me.Cells(lastRowToClear, rng.Column)).Clear
Next rng
End If

Application.EnableEvents = True
End Sub


Note that you can change keyRowRange to reflect whichever row the key
value is in. In the above, changing or deleting any cell in row 11
will clear the associated column. Likewise, you can change the values
for firstRowToClear and lastRowToClear - this version goes from 12 to
500 as you specified.

Let me know whether that works for you.



On Oct 19, 12:27 pm, PurpleMilk
wrote:
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 -- Hide quoted text -

- Show quoted text -