Drop Down List - if changed then make other cell blank

[ParanoidAndroid]

Hi Guys

I have a drop down list in D9. If one makes changes to this drop down then I want D10, D11, D12 and D13 to become blank.

Is it possible for code to do this?

I then want the same for D10..if change is made then i want D11, D12 and D13 to be blank

Same for D11, i want D12 and D13 to be blank.

Likewise for D12, if a change is made then D13 to be blank

Is this possible?

 

[AlphaFrog]

Right-click on the sheet tab that has your drop-down lists and select View Code.
Paste the code below in the VBA edit window.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(Range("D9:D12"), Target) Is Nothing Then
        With Intersect(Range("D9:D12"), Target)
            If .Cells(1) = "" Then
                Application.EnableEvents = False
                Range(.Cells(1), Range("D13")).ClearContents
                Application.EnableEvents = True
            End If
        End With
    End If
    
End Sub

 

[CBurgess]

Hate to bring this is up again but I've got the same problem, but I can't get the above code to work.

Basically I've got two drop down boxes. A) and B).

B)'s options will be dependant on what is previously selected in A), only problem is that when you've got A) and B) selected, you can change A) to a value which shouldn't be compatable with the value that's in B)

I was thinking the only way to get around this is by having drop down B) clear when A) is changed. This will stop the user manipulating the system to choose illegal options.

 

[AlphaFrog]

Hate to bring this is up again but I've got the same problem, but I can't get the above code to work.

Basically I've got two drop down boxes. A) and B).

B)'s options will be dependant on what is previously selected in A), only problem is that when you've got A) and B) selected, you can change A) to a value which shouldn't be compatable with the value that's in B)

I was thinking the only way to get around this is by having drop down B) clear when A) is changed. This will stop the user manipulating the system to choose illegal options.

• Right-click on the sheet tab that has your dependent Data Validation drop-down lists
• Select View Code From the pop-up context menu
• Paste the code below in the <acronym title="visual basic for applications">VBA</acronym> edit window
• Change the two cell references (A1 and B1) in the code to suit

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Change([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
    [COLOR=darkblue]If[/COLOR] Target.Address(0, 0) = "A1" [COLOR=darkblue]Then[/COLOR] Range("B1").ClearContents
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

This clears B1's value whenever A1 is changed.

 

[CBurgess]

 Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) = "D10:E10" Then Range("J10:K10").ClearContents
End Sub

This is what i've got, its not working at the moment but it says it can't change a merged cell?

 

[AlphaFrog]

 Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) = "D10:E10" Then Range("J10:K10").ClearContents
End Sub

This is what i've got, its not working at the moment but it says it can't change a merged cell?

This worked for me when D1:E10 are merged and J10:K10 are merged.

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Worksheet_Change([color=darkblue]ByVal[/color] Target [color=darkblue]As[/color] Range)
    [color=darkblue]If[/color] Target.Address(0, 0) = "D10" [color=darkblue]Then[/color] Range("J10:K10").ClearContents
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[CBurgess]

A-ha brilliant it works now. Thank you.

 

[rilzniak]

Hi AlphaFrog,

I've got the same problem...B3, B4, and B5 are drop-downs. B4 is dependant on B3 and B5 is dependant on B4 (and also by default on B3). I've used named ranges and the INDIRECT formula in the Data Validation to populate the drop-downs and when I paste the code you suggest above into a Module for this workbook it doesn't work...

Any suggestions?

 

[AlphaFrog]

I don't know which version of the code you tried.

Try this...

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(Range("B3:B5"), Target) Is Nothing Then
        With Intersect(Range("B3:B5"), Target)
            If .Cells(1) = "" Then
                Application.EnableEvents = False
                Range(.Cells(1), Range("B5")).ClearContents
                Application.EnableEvents = True
            End If
        End With
    End If
    
End Sub

 

[rilzniak]

Try this...

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(Range("B3:B5"), Target) Is Nothing Then
        With Intersect(Range("B3:B5"), Target)
            If .Cells(1) = "" Then
                Application.EnableEvents = False
                Range(.Cells(1), Range("B5")).ClearContents
                Application.EnableEvents = True
            End If
        End With
    End If
    
End Sub

No dice! Do I need to have a blank cell in my named reference ranges?