Reset dependent dropdown to "Please select" on change

J

juliecooper255

New Member
Joined
Apr 24, 2024
Messages
15
Office Version
  1. 365
Platform
  1. Windows
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.
www.mrexcel.com

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 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"
 

Attachments

  • Screenshot 2024-04-15 124430.png
    Screenshot 2024-04-15 124430.png
    23.5 KB · Views: 13

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
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.
www.mrexcel.com

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 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.
 
Upvote 0
Welcome to the forum, perhaps something like the below placed in the worksheet module:
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
   
    If Target.Column < 9 Then
        Range(Cells(Target.Row, Target.Column + 1), Cells(Target.Row, 9)) = "Please Select"
    End If
End Sub

Or:
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    
    If Target.Column < 9 Then
        Target.Offset(, 1).Resize(, 9 - Target.Column) = "Please Select"
    End If
End Sub
 
Upvote 0
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)
1714214345178.png
 
Upvote 0
@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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Dependent dynamic list with offset/countif formula and name manager
Replies
1
Views
695
juliecooper255
J
J
  • Question Question
Dropdown dependent dropdown based upon multiple selections.
Replies
0
Views
548
jcschafer209
J
R
  • Question Question
Smarter alternative to Name Manager & Indirect Formula?
Replies
1
Views
480
RoryA
RoryA
D
  • Solved
Cannot COUNT/SUM Displayed Table Values, Table Filtered Values Out of Order
Replies
3
Views
387
Alex Blakenburg
A
M
  • Question Question
VBA vs Formulas/Data Validation for Multiple Dependent Dropdown Lists
Replies
3
Views
2K
MissDB101
M

Forum statistics

Threads
1,224,812
Messages
6,181,104
Members
453,021
Latest member
Justyna P

Share this page

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
Back
Top