Reset dependent dropdown to "Please select" on change

juliecooper255 said:

Hi!
I followed the instraction in the thread below but unsuccessfully. I suspect it could be because my drop down are linear rather than column.

Reset Cell Values Based on Change to Various Cells

Good evening. I have a challenging problem I was hoping to get help with. I have reviewed other posts and found a solution that allows for clearing a range of cells based on a change, but can't find a way to clear different ranges based on different cells changing. I have dependent drop-downs...

www.mrexcel.com

Here is what I am trying to achieve:

Dropdown list in column D (all lines from D16) - When changed, reset all dropdowns in columns E,F,G,H to "Please Select"
Dropdown list in column E (all lines from E16) - When changed, reset all dropdowns in columns F,G,H to "Please Select"
Dropdown list in column F (all lines from F16)- When changed, reset all dropdowns in columns G,H to "Please Select"
Dropdown list in column G (all lines from G16)- When changed, reset all dropdowns in columns H to "Please Select"

Click to expand...

I would greatly appreciate if someone could help me with the VBA code. What I tried didn't work.

Welcome to the forum, perhaps something like the below placed in the worksheet module:

Or:

Amazing, worked a charm!

Hi Georgyboy and community, I am trying to tweak this code slightly so it work a little better with my data set, but I haven't managed to go very far. I am not understanding the syntax of this code well so it make it a little harder to figure. Could you kindly help me out again?

What I need to achieve is:
1- when any cell in column E from E15 changes, clear content in column F,G & H only on the same line. (ei, change in E23, clear F23,G23,H23 only)
2- when any cell in Column F from F15 changes, clear content in the column G & H only on the same line. (ei, change in F18, clear G18 & H18 only)
3-Finally, when any cell in Column G changes, clear content in column H on the same line (ei, Change in G30, clear content in H30)

@juliecooper255 Perhaps give this a try.

Snakehips said:

@juliecooper255 Perhaps give this a try.

VBA Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long

    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Row < 16 Then Exit Sub
   r = Target.Row
   Beep
   Select Case Target.Column
     Case 5
         Range("F" & r & ":H" & r).ClearContents
     Case 6
         Range("G" & r & ":H" & r).ClearContents
     Case 7
         Range("H" & r).ClearContents
   End Select
 
End Sub

Click to expand...

Thanks, i am starting to make sense of syntax as well. Unfortunately, it doesn't work. And testing the code from Georgyboy that was working yesterday, it is not working anymore either. Would other code interfere with it? So far, this is what I have. All work apart from the reset.



Sub Hide_costing()
'
' Hide_costing Macro
'
' Keyboard Shortcut: Ctrl+h
'
Columns("D:K").Select
Selection.EntireColumn.Hidden = True
ActiveWorkbook.Connections("Query - Cost Items").Refresh
Sheets("SOW").Select
Range("B15").Select
End Sub


Sub Print_Proposal()
'
' Print_Proposal Macro
'
' Keyboard Shortcut: Ctrl+p
'
Sheets("Proposal").Select
ActiveWindow.SelectedSheets.Delete
Dim ws As Worksheet
Set ws = Worksheets.Add
ws.Name = "Proposal"
Range("F23").Select
ActiveWindow.DisplayGridlines = False
Sheets("SOW").Select
Columns("B:X").Select
Range("X1").Activate
Selection.Copy
Sheets("Proposal").Select
Columns("A:A").Select
ActiveSheet.Paste
Columns("B:M").Select
Range("M1").Activate
Selection.EntireColumn.Hidden = False
Columns("C:J").Select
Range("J1").Activate
Selection.EntireColumn.Hidden = True
Columns("M:N").Select
Selection.EntireColumn.Hidden = True
Columns("O:S").Select
Selection.EntireColumn.Hidden = True
Columns("V:V").Select
Selection.EntireColumn.Hidden = True
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Columns("A:A").ColumnWidth = 29
ActiveWindow.SmallScroll Down:=51
Columns("B:B").ColumnWidth = 63.86
ActiveWindow.SmallScroll Down:=40
Columns("A:W").Select
Range("W92").Activate
Application.CutCopyMode = False
ActiveSheet.PageSetup.PrintArea = "$A:$W"
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
Application.PrintCommunication = True
ActiveSheet.PageSetup.PrintArea = "$A:$W"
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.7)
.RightMargin = Application.InchesToPoints(0.7)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.3)
.FooterMargin = Application.InchesToPoints(0.3)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 0
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
.EvenPage.LeftHeader.Text = ""
.EvenPage.CenterHeader.Text = ""
.EvenPage.RightHeader.Text = ""
.EvenPage.LeftFooter.Text = ""
.EvenPage.CenterFooter.Text = ""
.EvenPage.RightFooter.Text = ""
.FirstPage.LeftHeader.Text = ""
.FirstPage.CenterHeader.Text = ""
.FirstPage.RightHeader.Text = ""
.FirstPage.LeftFooter.Text = ""
.FirstPage.CenterFooter.Text = ""
.FirstPage.RightFooter.Text = ""
End With
Application.PrintCommunication = True
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
Sheets("SOW").Select
End Sub



Sub Budget_Costing()
'
' Budget_Costing Macro
'
' Keyboard Shortcut: Ctrl+b
'
ActiveWorkbook.Connections("Query - Cost Items").Refresh
Application.CommandBars("Queries and Connections").Visible = False
Sheets("SOW").Select
Columns("C:N").Select
Selection.EntireColumn.Hidden = False
Columns("L:M").Select
Selection.EntireColumn.Hidden = True
End Sub



Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long

If Target.Cells.Count > 1 Then Exit Sub
If Target.Row < 16 Then Exit Sub
r = Target.Row
Beep
Select Case Target.Column
Case 5
Range("F" & r & ":H" & r).ClearContents
Case 6
Range("G" & r & ":H" & r).ClearContents
Case 7
Range("H" & r).ClearContents
End Select

End Sub

Ok. First off I would suggest that my code will benefit from the small edit below.

My first thought for my code and your previous code not working would be that whilst testing you may have had a code line 'Application.EnableEvents = False'
That always needs to be reset before the code ends with the code line 'Application.EnableEvents = True'
If you have had such a code error out during testing, before reaching the re-set line then events such as Worksheet_Change will not get triggered.

You can do a re-set by typing Application.EnableEvents = True in the Immediate pane of the VBA editor and hitting return.

Try that first and see if it sorts it.

Thanks, I modified the code and rerun it, but still not working.

Snakehips said:

Ok. First off I would suggest that my code will benefit from the small edit below.

VBA Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long

    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Row < 16 Then Exit Sub
   r = Target.Row
   Application.EnableEvents = False
   Select Case Target.Column
     Case 5
         Range("F" & r & ":H" & r).ClearContents
     Case 6
         Range("G" & r & ":H" & r).ClearContents
     Case 7
         Range("H" & r).ClearContents
   End Select
   Application.EnableEvents = True
End Sub

My first thought for my code and your previous code not working would be that whilst testing you may have had a code line 'Application.EnableEvents = False'
That always needs to be reset before the code ends with the code line 'Application.EnableEvents = True'
If you have had such a code error out during testing, before reaching the re-set line then events such as Worksheet_Change will not get triggered.

You can do a re-set by typing Application.EnableEvents = True in the Immediate pane of the VBA editor and hitting return.

Try that first and see if it sorts it.

Click to expand...

Application.EnableEvents = True remains True