On change of key

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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).
 
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.
 
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.
 
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.
 
I tried the code and there's an error starting after "For Each rng In
Target".

I'm still a beginner at VBA, so not sure what it's trying to do.
 
The For Each part accounts for the possibility that multiple keys are
deleted from the key row at once. It goes through each cell in the
affected range (Target), and clears the corresponding column, starting
in row firstRowToClear through lastRowToClear.

What is the nature of the error you're getting?
 
I figured out the prob..... it's my bad ... I have security on to only let
users tab to cells they need to. I had to adjust it.

All's good now. Thank's for all your patience and help.
Take care, Mary-Lou
 
One more caveat - sorry I missed it earlier. For multiple selections,
the procedure can be quite slow, and could potentially freeze Excel.

To circumvent this issue, find this line:
If Not (Intersect(Target, keyRowRange) Is Nothing) Then

Insert this line directly below it:
Set Target = Intersect(Target, keyRowRange)

Another problem with the code occurs if an entire column is deleted.
In that situation, the adjacent column will be cleared, which is not
desired behavior. So, here's a reworked procedure code, which is more
"elegant" in my estimation, and also avoids the column deletion
issue. I also added comments to clarify exactly what's going on.


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

' define row range to be cleared
Const firstRowToClear As Long = 12
Const lastRowToClear As Long = 500

' this ensures that we don't fall into an infinite loop
Application.EnableEvents = False

' define the row address where key values are contained
' replace this with a selection such as B11:J11
' to limit columns affected by this procedure
Set keyRowRange = Me.Range("11:11")

' check to ensure user did not delete the entire column;
' if this is the case, we're done
If Target.Address = Target.EntireColumn.Address Then
GoTo TheEnd
End If

' ensure each column is cleared only once
Set Target = Intersect(Target, keyRowRange)

' if no cells in keyRowRange are being changed, we're done
If (Target Is Nothing) Then GoTo TheEnd

' process each column where the key row has been changed
For Each rng In Target
Me.Range(Me.Cells(firstRowToClear, rng.Column), _
Me.Cells(lastRowToClear, rng.Column)).Clear
Next rng

TheEnd:

' re-enable events to make sure the procedure fires again
Application.EnableEvents = True
End Sub
 
Back
Top