Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 428
  • Last Modified:

Macro to Reset Cascading Drop Downs if "Children" don't belong to "Parents"

Hi Experts,

I found this on the interwebs: http://chandoo.org/wp/2014/02/13/dynamic-cascading-dropdowns-that-reset/ 
However, the method that was used to achieve the cascading seems unnecessarily more complicated/laborious than the method that I used in the attached example. As such, chandoo's refresh macro won't work.

I was wondering if a similar macro could be coded to accomplish a the refresh effect on my attached example when children do not match parents. If it's too complicated or not possible with how I achieved the cascading let me know and I'll follow Chandoo's tutorial from start to finish.
EE-Safe-Copy-Macro.xlsm
0
-Polak
Asked:
-Polak
  • 4
  • 2
1 Solution
 
Glenn RayExcel VBA DeveloperCommented:
Yes; you could modify the Worksheet_Change events to clear "children" cells if a parent cell is changed (here's an example in the StandardMode sheet object):
Private Sub Worksheet_Change(ByVal Target As Range)
    If CascadeSelections Then Exit Sub
    Select Case Left(Target.Address, 4)
        Case "$M$2"
            CascadeSelections = True
            Range("M3:P4").ClearContents
            Sheets("ManualMode").Range("M2").Value = Target.Value
            Sheets("ManualMode").Range("M3:P4").ClearContents
        Case "$M$3"
            CascadeSelections = True
            Range("M4:P4").ClearContents
            Sheets("ManualMode").Range("M3").Value = Target.Value
            Sheets("ManualMode").Range("M4:P4").ClearContents
        Case "$M$4"
            CascadeSelections = True
            Sheets("ManualMode").Range("M4").Value = Target.Value
    End Select
    CascadeSelections = False
End Sub

Open in new window


Note that because of merged cells, columns M through P are edited.

Updated workbook attached.

-Glenn
EE-Safe-Copy-Macro-mod.xlsm
0
 
-PolakAuthor Commented:
Hi Glenn, simple enough. There would never be a situation where a branch would exist in more than one Region. However, I was under the impression that chandoo's code only reset a child if it didn't belong to a parent, is that not the case?

(You do not have to adapt his code to accomplish this, just curious if that can be done the way I have cascading set up)
0
 
Glenn RayExcel VBA DeveloperCommented:
Chandoo's code (below) will clear any values if the validation rule uses the "SubList" dynamic range (lines 22-23), regardless of whether the child values are similar.
Option Explicit
Const CHOOSE = "Choose…"
Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo ErrorHandler
    Dim targetCell As Range
    Dim nextCell As Range
    Dim oldCalc As Excel.XlCalculation
    
    If Not Intersect(Target, [DataEntryTable]) Is Nothing Then
        If [Radio_Choice] = 1 Then
            With Application
                .EnableEvents = False
                .ScreenUpdating = False
                oldCalc = .Calculation
                .Calculation = xlCalculationManual
            End With
            
            For Each targetCell In Target
                'Clear any cells that use 'SubList' to the right of targetCell in the current table.
                If targetCell.Column < (targetCell.ListObject.ListColumns.Count + targetCell.ListObject.Range.Column - 1) Then 'there are table cells to the right
                    For Each nextCell In targetCell.Offset(, 1).Resize(, targetCell.ListObject.ListColumns.Count + targetCell.ListObject.Range.Column - targetCell.Column - 1)
                        If HasValidationFormula(nextCell) Then
                            If nextCell.Validation.Formula1 = "=SubList" Then nextCell.Value = ""
                        End If
                    Next nextCell
                End If
                
                'Perform different action depeding on whether we're dealing with a 'MainList' dropdown
                ' or a 'SubList' dropdown
                If HasValidationFormula(targetCell) Then
                    Select Case targetCell.Validation.Formula1
                    Case "=MainList"
                        If targetCell.Value = "" Then
                            targetCell.Value = CHOOSE
                        ElseIf targetCell.Value = CHOOSE Then
                            'Do nothing.
                        Else
                            targetCell.Offset(, 1).Value = CHOOSE
                        End If
                        
                    Case "=SubList"
                        If targetCell.Value = "" Then
                            targetCell.Value = CHOOSE
                        ElseIf targetCell.Offset(, -1).Value = CHOOSE Then
                            targetCell.Value = ""
                        ElseIf targetCell.Value = CHOOSE Then
                            'Do nothing
                        Else
                            Set nextCell = targetCell.Offset(, 1)
                            If HasValidationFormula(nextCell) Then
                                If nextCell.Validation.Formula1 = "=SubList" Then nextCell.Value = CHOOSE
                            End If
                        End If
                    End Select
                End If
            Next targetCell
            With Application
                .EnableEvents = True
                .ScreenUpdating = True
                .Calculation = oldCalc
            End With
        End If
    End If
    Exit Sub
ErrorHandler:
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
        If oldCalc <> 0 Then .Calculation = oldCalc
    End With
    MsgBox Err.Description, vbCritical, Name & ".Worksheet_Change()"
End Sub

Private Function HasValidationFormula(cell As Range) As Boolean
    On Error GoTo ValidationNotExistsError
    If cell.Validation.Formula1 <> "" Then
        HasValidationFormula = True
    Else
        HasValidationFormula = False
    End If
    Exit Function
ValidationNotExistsError:
    HasValidationFormula = False
End Function

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
-PolakAuthor Commented:
Thanks sir, way easier than what I was thinking.
0
 
-PolakAuthor Commented:
Hi Glenn, I've noticed a bug in this code that I can't seem to explain. Please see the attachment in your accepted solution.

Next, select a "region" and "branch", and type in a "shift" to cascade across sheets.
Then, make an entry anywhere in M20:M29, you'll notice that branch and shift will clear.
Similar issues exist if you make entries in M30:M39 where only shift will clear.....

In the production version of this workbook, I have the usual problem of the values that I enter into column M actually duplicating inside of Branch, Region, and Shift up at the top. What's causing this?
0
 
-PolakAuthor Commented:
As a Follow-up Adding:
If Not Intersect(Target, Range("M2:P4")) Is Nothing Then

Open in new window

before the Select Case has resolved the issue...
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now