VBA code for clearing multiple indep/depen cell groups

[PTSkiBum]

Hi all,

I found the following VBA code to clear a dependent drop down menu entry when the independent drop down entry is changed:


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("A1")) Is Nothing Then
Range("B1").ClearContents
End If

End Sub
</pre>

It works perfectly when I change A1...B1 is cleared. However, I have independent drop down data in A1 through A100, and I need the code to work in each row. In other words, when I change A4, I want B4 (only) to clear while the rest of the rows/columns go unchanged. If I change A30, I want B30 to clear. Can someone please help me properly define the Range in the above code?

Thanks in advance for your time.

 

[pgc01]

Hi
Welcome to the board

Using this code, if you change the value in any cell in column A, the cell to the right is cleared.

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
If Target.Cells.Count > 1 Then Exit Sub
 
Application.EnableEvents = False
If Target.Column = 1 Then Target.Offset(0, 1).ClearContents
Application.EnableEvents = True
End Sub

 

[Trebor76]

Hi there,

Try this:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub
    
    If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
        Range("B" & Target.Row).ClearContents
    End If

End Sub

Robert

 

[pgc01]

Hi Robert

It's good practice to disable events when you change the value of a cell in an event procedure.

In this case when you clear the cell in column B, this will trigger the change event again, the procedure is interrupted and called again before it finished its execution and then later resumed.

Try adding your code 2 messages to see this happening:

Private Sub Worksheet_Change(ByVal Target As Range)
 
    MsgBox "1"
    
    If Target.Cells.Count > 1 Then Exit Sub
    
    If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
        Range("B" & Target.Row).ClearContents
    End If
 
    MsgBox "2"
    
End Sub

When you change a value in column A you'll see from the sequence of the messages what happens, instead of a clean (1,2) you get (1,1,2,2).

 

[PTSkiBum]

Thanks for the lightning fast response from both of you.

PGC, I tried your code with success. Just one follow-up. If my independent data is in say column C (with D depend) instead of A/B, how does the code change? Please forgive the newbie questions, I haven't used VBA coding before.

Kind regards,
Paul

Hi
Welcome to the board

Using this code, if you change the value in any cell in column A, the cell to the right is cleared.

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
If Target.Cells.Count > 1 Then Exit Sub
 
Application.EnableEvents = False
If Target.Column = 1 Then Target.Offset(0, 1).ClearContents
Application.EnableEvents = True
End Sub

 

[PTSkiBum]

Oh, I forgot to mention that Robert's code did not work for me, though I can't explain why.

Plus, since trying Robert's code, PGC's no longer works. ...grrr...

 

[pgc01]

Oh, I forgot to mention that Robert's code did not work for me, though I can't explain why.

Plus, since trying Robert's code, PGC's no longer works. ...grrr...

Hi

Robert's code works.

Maybe you have the events disabled

Execute this statement, for example in the Immediate Window:

Application.EnableEvents = True

 

[PTSkiBum]

PGC,

I added your suggested line to the Immediate window, but Robert's code still isn't working for me. Perhaps I'm doing something else wrong. Would I be taking advantage of your kindness if I sent you my excel file to spot check? I know it's difficult to troubleshoot these things in the abstract.

Regards,
Paul

 

[Trebor76]

Hi Paul,

My code worked fine for me also

Note, as per your original posting, my code will only attempt to only clear contents in B1:B100 where a change has only occurred in A1:100.

Hi pcg01,

Good point you raise!! Considering though that the actual range firing the code is not being affected by the code the following should do the trick without having to worry about disabling events, i.e.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
        Range("B" & Target.Row).ClearContents
    End If
       
End Sub

Regards,

Robert

 

[PTSkiBum]

Hi Robert,

Understood. I was testing on column A, even though I need it to work on D. I figured I would edit your code if I could get it working, but no luck so far. I'm sure there is something else wrong with my worksheet, but I can't figure it out.

-Paul