VBA to clear value of Dependent drop downs when selection changes in parent drop down

Latha

Board Regular
Joined
Feb 24, 2011
Messages
146
[FONT=&quot]hi, i have the below code to clear value of Dependent drop downs (C13:C22) when selection changes in parent drop downs(B12:B22) but i have an issue. i have rows with some data above B13 and when there is a change in the any of cell between B1 to B12 the corresponding column values are also becoming blank. Please guide me how do i restrict tthe below code to make dependent list blank for cells C13:C22 only.[/FONT]
[FONT=&quot]Private Sub Worksheet_Change(ByVal Target As Range)[/FONT]
[FONT=&quot]‘clear contents of dependent cells
Rich (BB code):
Dim sht As Worksheet
Set sht = ThisWorkbook.Sheets(“Billing”)[/FONT]
Rich (BB code):
[FONT=&quot]On Error Resume Next
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Select Case Target.Column[/FONT]
[FONT=&quot]Case 2 ‘clear columns C and D
Target.Offset(0, 1).ClearContents
‘ Target.Offset(0, 2)).ClearContents
‘ Case 3 ‘clear column D
‘ Target.Offset(0, 1).ClearContents
End Select
End If[/FONT]
[FONT=&quot]exitHandler:
Application.EnableEvents = True
Exit Sub[/FONT]
[FONT=&quot]End Sub
[/FONT]
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

AFPathfinder

Well-known Member
Joined
Apr 8, 2012
Messages
519
I think if you just add a second condition to the If statement, that should take care of the issue.
Code:
If Target.Validation.Type = 3 [COLOR=#0000ff]And Target.Row > 12 [/COLOR]Then

Altogether:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'clear contents of dependent cells
    Dim sht As Worksheet
    Set sht = ThisWorkbook.Sheets(“Billing”)
    
    On Error Resume Next
    If Target.Validation.Type = 3 [COLOR=#0000ff]And Target.Row > 12 [/COLOR]Then
        Application.EnableEvents = False
        Select Case Target.Column
        Case 2 'clear columns C and D
            Target.Offset(0, 1).ClearContents
            ' Target.Offset(0, 2)).ClearContents
        ' Case 3 ‘clear column D
            ' Target.Offset(0, 1).ClearContents
        End Select
    End If
exitHandler:
    Application.EnableEvents = True
    Exit Sub
End Sub
 

Latha

Board Regular
Joined
Feb 24, 2011
Messages
146
Thanks. but its still changing values for rows 8 to 11

I think if you just add a second condition to the If statement, that should take care of the issue.
Code:
If Target.Validation.Type = 3 [COLOR=#0000ff]And Target.Row > 12 [/COLOR]Then

Altogether:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'clear contents of dependent cells
    Dim sht As Worksheet
    Set sht = ThisWorkbook.Sheets(“Billing”)
    
    On Error Resume Next
    If Target.Validation.Type = 3 [COLOR=#0000ff]And Target.Row > 12 [/COLOR]Then
        Application.EnableEvents = False
        Select Case Target.Column
        Case 2 'clear columns C and D
            Target.Offset(0, 1).ClearContents
            ' Target.Offset(0, 2)).ClearContents
        ' Case 3 ‘clear column D
            ' Target.Offset(0, 1).ClearContents
        End Select
    End If
exitHandler:
    Application.EnableEvents = True
    Exit Sub
End Sub
 

Latha

Board Regular
Joined
Feb 24, 2011
Messages
146
i fixed this by removing "Target.Validation.Type = 3" from IF condition and its working fine now. Thanks a lot for the help

Rich (BB code):
Private Sub Worksheet_Change(ByVal Target As Range)
'clear contents of dependent cells
    Dim sht As Worksheet
    Set sht = ThisWorkbook.Sheets(“Billing”)
    
    On Error Resume Next
    If Target.Row > 12 Then
        Application.EnableEvents = False
        Select Case Target.Column
        Case 2 'clear columns C and D
            Target.Offset(0, 1).ClearContents
            ' Target.Offset(0, 2)).ClearContents
        ' Case 3 ‘clear column D
            ' Target.Offset(0, 1).ClearContents
        End Select
    End If
exitHandler:
    Application.EnableEvents = True
    Exit Sub
End Sub
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,174
Members
417,130
Latest member
Darion2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top