dependent validation lists fire worksheet change event

ajm · Sep 14, 2011

is it possible to have a worksheet change event triggered by the selection of an option button? where i am at today: i have an event that fires when the user selects from two validation lists on the worksheet. the second validation list (customer accounts) is dependent upon the first (account managers). the resulting selection, of account manager and one of their accounts, is then used to filter two pivot tables to compare this years to last years sales.

the worksheet change event fires when either of the two cells is changed. the code is below:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim critRange As Range, c As Range, ws As Worksheet, PT As PivotTable
    Set critRange = Range("C2:C3")
    If Intersect(Target, critRange) Is Nothing Then GoTo CleanUp

    Dim pi As PivotItem
    Dim i As Long, j As Long
    Dim strFields() As String, strValue As String
    Dim graphSheets As Variant
    strFields = Split("Master Account Manager;Debtor Normal Name", ";")
    Set graphSheets = Sheets(Array("Rep Sales Data", "TY Sales Data"))
    
    On Error GoTo CleanUp
    Application.EnableEvents = False
    Application.ScreenUpdating = False

    For Each ws In graphSheets 'Sheets(Array("Rep Sales Data", "TY Sales Data"))
        'With Worksheets("Rep Sales Data").PivotTables("PivotTableRepSaleData")
        For i = 1 To critRange.Rows.Count
            strValue = critRange(i).Value
            For Each PT In ws.PivotTables
            With PT.PivotFields(strFields(i - 1))
                Select Case .Orientation
                Case xlPageField
                    .ClearAllFilters
                    For Each pi In .PivotItems
                        If pi.Value = strValue Then
                            .CurrentPage = strValue
                            Exit For
                        Else
                            .CurrentPage = "(All)"
                        End If
                    Next pi
                Case Else
                    .PivotItems(strValue).Visible = True
                    For j = 1 To .PivotItems.Count
                        If .PivotItems(j) <> strValue And _
                           .PivotItems(j).Visible = True Then
                            .PivotItems(j).Visible = False
                        End If
                    Next j
                End Select
            End With
            Next PT
        Next i
    Next ws

    Application.Calculate
CleanUp:
    Application.EnableEvents = True
End Sub

how do i get the second list (customers) to reset to All when the account manager is changed? the underlying pivot tables reset the second filter to all when the worksheet event runs but this doesn't carry through to my active worksheet.



<table border="1" bordercolor="#C0C0C0" bordercolordark="#FFFFFF" cellspacing="0" cellpadding="2" align="left">
<tr><th bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"> </th><th bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"> </th><th bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"> </th><th bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"> </th></tr>
<tr><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"> </td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap">Master Account Manager</td><td bgcolor="#FFFFFF" valign="bottom" style="white-space: nowrap">UNALLOCATED</td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"> </td></tr>
<tr><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"> </td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap">Select Customer Group</td><td bgcolor="#FFFFFF" valign="bottom" style="white-space: nowrap">AKSU KEBAB</td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"> </td></tr>
<tr><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"> </td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap">Select Customer Account</td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"> </td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"> </td></tr>
<tr><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"> </td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"> </td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"> </td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"> </td></tr>
</table>

so, when the Master Account Manager is changed, the cell below it should reset to "All" rather than holding the previously selected customer name.

repairman615 · Sep 14, 2011

Hello,

Yes, an option button can be an event.

Using an ActiveX option button, right click the option button and select veiw code.

Private Sub OptionButton1_Click() End Sub

ajm · Sep 14, 2011

thanks for that jeff. i missed that first line when i edited my inital post. i forgot to delete it. i think using something to "reset" my second validation list will be a better method for my immediate needs

ajm · Sep 15, 2011

i have struggled for the last couple of days to amend this worksheet change event and am at my wits end. currently, the code is used to change the filters on two pivot tables
when the user changes the selections in two cells on sheet "Annual Results".
the cells contain Account Manager and then the customers managed by the selected account manager. the values in the second are dependent upon the selection in the first cell.
I do not completely understand the way the code works so have been unsuccesful with the change attempts i have made. the result i want to achieve is that when the account manager is changed, the customer list in the second cell displays the value "All". then, if the user chooses to proceed they may select a particular customer from the particular account manager's list. basically, if they want to look at the account managers efforts overall, they would just select the relevant name in the first drop down. this would effectively clear the filters in the Customer accounts field (Debtor Normal Name) in both pivot tables. If the user wanted to see how a particular customer account is being managed, they would first select the account manager and then continue to select the customer form the second validation list.

any help would be greatly appreciated.

ajm · Sep 15, 2011

repairman615 · Sep 16, 2011

I have not used pivot tables. I have looked at the code and it gets confusing with all the variables and references to pivot table properties and objects of which I haven't used.

You mentioned two pivot tables. What sheet is each pivot table on?

What sheet is this code on? (if on a different sheet than you customer p/t)

Is there a specific cell that when changed should trigger the customers filter to all?

I have some ideas, but I can't say any one will absolutly work.

ajm · Sep 18, 2011

hi repairman. it is confusing, and at first, even more so. the code for pivot tables is new to me but it could be very useful i think.

what this code does is

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim critRange As Range, c As Range, ws As Worksheet, PT As PivotTable
    Set critRange = Range("C2:C3") '/// changes in these two will fire this event
    If Intersect(Target, critRange) Is Nothing Then GoTo CleanUp '/// if change not the target range, go to Clean UP

    Dim pi As PivotItem
    Dim i As Long, j As Long
    Dim strFields() As String, strValue As String
    Dim graphSheets As Variant
    
    strFields = Split("Master Account Manager;Debtor Normal Name", ";") '///don't properly understand Split, except
    '/// it seems to allow the variable strFields to accept variable from both C2 & C3
    Set graphSheets = Sheets(Array("Rep Sales Data", "TY Sales Data")) '/// pivot tables on these sheets

    On Error GoTo CleanUp
    Application.EnableEvents = False
    Application.ScreenUpdating = False

    For Each ws In graphSheets
        For i = 1 To critRange.Rows.Count '/// critRange is two cells C2 & C3, so C2 = 1 and C3 =2
            strValue = critRange(i).Value '///first loop gives (i) the value of 1, 2nd loop 2
            For Each PT In ws.PivotTables '/// for each of the pivot tables on each of the sheets in the array

                With PT.PivotFields(strFields(i - 1)) '/// i think this somehow ties in to the use of split above
                '/// as split definition refers to a zero based, one dimensional array?
                    Select Case .Orientation '/// not really sure
                    Case xlPageField '///if case is xlpagefield, clear all filters
                        .ClearAllFilters
                        For Each pi In .PivotItems '///now look at items in val list (c2)
                            If pi.Value = strValue Then
                                .CurrentPage = strValue '///make the page filter equal to selection from val list
                                Exit For
                            Else
                                .CurrentPage = "(All)" '///reverts to All until there is a match
                            End If
                        Next pi

                    Case Else
                        .PivotItems(strValue).Visible = True
                        For j = 1 To .PivotItems.Count
                            If .PivotItems(j) <> strValue And _
                               .PivotItems(j).Visible = True Then
                                .PivotItems(j).Visible = False '///not entirely sure here except that if a change has been made to C3
                                '/// this loops through each pivot item in the available list and if it differs to C3, hide it.
                            End If
                        Next j
                    End Select
                End With
            Next PT
        Next i
    Next ws

    Application.Calculate
CleanUp:
    Application.EnableEvents = True
End Sub

so, my validation drop downs are on sheet "Annual Results" and so holds the change event code. the cells C2 (Master Account Manager) and C3 (Debtor Normal Name) on this sheet trigger the event. the pivot tables are on two sheets entitled, "TY Sales Data", and "Rep Sales Data".

I look forward to hearing your ideas. many thanks for having a look

repairman615 · Sep 19, 2011

Hello ajm,

Here may be the first attempt of many. May we learn form out mistakes.

If nothing else we will keep bumping this to the top. I am certain this can be done and if not by either of us stumbling upon the solution, then surely another whom is more familiar with p/t code.

If I have time tomorrow I will try to simulate a workbook so I could test the code. If the WB is not proprietary and does not contain sensitive information, maybe upload to box.net or other site.

I have tried a seperate logical test to isolate cell C2 for change and if so, changed C3 to the string "All".

I am not sure if this will change the filter on the pivot table, just seemed like a good starting point to me.

Private Sub Worksheet_Change(ByVal Target As Range) Dim critRange As Range, c As Range, ws As Worksheet, PT As PivotTable Set critRange = Range("C2:C3") '/// changes in these two will fire this event If Intersect(Target, critRange) Is Nothing Then GoTo CleanUp '/// if change not the target range, go to Clean UP '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' If Not Intersect(Target, Range("C2")) Is Nothing Then Range("C3").Value = "All" '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Dim pi As PivotItem Dim i As Long, j As Long Dim strFields() As String, strValue As String Dim graphSheets As Variant strFields = Split("Master Account Manager;Debtor Normal Name", ";") '///don't properly understand Split, except '/// it seems to allow the variable strFields to accept variable from both C2 & C3 Set graphSheets = Sheets(Array("Rep Sales Data", "TY Sales Data")) '/// pivot tables on these sheets On Error GoTo CleanUp Application.EnableEvents = False Application.ScreenUpdating = False For Each ws In graphSheets For i = 1 To critRange.Rows.Count '/// critRange is two cells C2 & C3, so C2 = 1 and C3 =2 strValue = critRange(i).Value '///first loop gives (i) the value of 1, 2nd loop 2 For Each PT In ws.PivotTables '/// for each of the pivot tables on each of the sheets in the array With PT.PivotFields(strFields(i - 1)) '/// i think this somehow ties in to the use of split above '/// as split definition refers to a zero based, one dimensional array? Select Case .Orientation '/// not really sure Case xlPageField '///if case is xlpagefield, clear all filters .ClearAllFilters For Each pi In .PivotItems '///now look at items in val list (c2) If pi.Value = strValue Then .CurrentPage = strValue '///make the page filter equal to selection from val list Exit For Else .CurrentPage = "(All)" '///reverts to All until there is a match End If Next pi Case Else .PivotItems(strValue).Visible = True For j = 1 To .PivotItems.Count If .PivotItems(j) <> strValue And _ .PivotItems(j).Visible = True Then .PivotItems(j).Visible = False '///not entirely sure here except that if a change has been made to C3 '/// this loops through each pivot item in the available list and if it differs to C3, hide it. End If Next j End Select End With Next PT Next i Next ws Application.Calculate CleanUp: Application.EnableEvents = True End Sub

ajm · Sep 19, 2011

Jeff,

I tried the isolation method as you have written above. However, while it changes the displayed value in C3 to "All", it only effects the first pivot table on "Rep Sales Data" tab. The Master Account Manager is correctly changed to the selection made in C2, but the Debtor Normal Name is not changed. Nothing on the second PT on "TY Sales Data" tab is changed. so essentially, the isolation that you suggested changes the initial account manager field on the first pivot table and then also the text that shows in C3 on the Annual Results tab.

I have joined box.net and once i get home, will activate the account and load up an example workbook for us to play with.

cheers ajm

ajm · Sep 19, 2011

Jeff, i have had a breakthrough. using two combo boxes instead of validation lists, i have bastardised the code originally written and now have two macros: the first looks at the Master Account Manager and also sets the linked cell of the second to "0". i put an iferror formula

=IFERROR(INDEX(SalesNameNormNameListActive,D3),"All Accounts")

in C3 to show the user that if they don't go on to make a second selection (Debtor), they will see "All Accounts" for the chosen Master Account Manager.

Then the second combo box just changes the pivot items visible in the Debtor Normal Name field of the pivot tables.

Code:

Sub ChooseMAM()

    Dim critRange As Range, c As Range, ws As Worksheet, PT As PivotTable, pi As PivotItem
    Set critRange = Worksheets("Annual Results").Range("C2")
    Dim strFields As String, strFields2 As String
    Dim graphSheets As Variant
    strFields = Worksheets("Annual Results").Range("b2").Value
    strFields2 = "Debtor Normal Name"

    strValue = critRange.Value
    Worksheets("Annual Results").Range("D3").Value = "0"

    Set graphSheets = Sheets(Array("Rep Sales Data", "TY Sales Data"))

    For Each ws In graphSheets
        For Each PT In ws.PivotTables
            With PT.PivotFields(strFields2)
                .ClearAllFilters
            End With

            With PT.PivotFields(strFields)
                .ClearAllFilters
                For Each pi In PT.PivotFields(strFields).PivotItems
                    If pi.Value = strValue Then
                        .CurrentPage = strValue
                        Exit For
                    Else
                        .CurrentPage = "(All)"
                    End If
                Next pi
            End With
        Next PT
    Next ws


End Sub

Sub ChooseAccount()

    Dim AccountRange As Range, c As Range, ws As Worksheet, PT As PivotTable, pi As PivotItem
    Dim strDebtorName As String, strFields2 As String
    Dim graphSheets As Variant
    Dim j As Long

    strFields2 = "Debtor Normal Name"
    Set AccountRange = Worksheets("Annual Results").Range("c3")
    strDNValue = AccountRange.Value

    Set graphSheets = Sheets(Array("Rep Sales Data", "TY Sales Data"))

    For Each ws In graphSheets
        For Each PT In ws.PivotTables

            With PT.PivotFields(strFields2)
                For Each pi In PT.PivotFields(strFields2).PivotItems

                    For j = 1 To .PivotItems.Count
                        If .PivotItems(j) <> strDNValue And _
                           .PivotItems(j).Visible = True Then
                            .PivotItems(j).Visible = False
                        End If
                    Next j

                Next pi
            End With
        Next PT
    Next ws


End Sub

its past 5pm here so i will leave the tidy up of this until tomorrow. it still needs further refinement and testing properly, but should get me across the line so i can put this one to bed.

do you see any potential problems going this route instead of the worksheet change event way?

cheers

ajm

PS how do you get your code to show up so nicely on the boards? i use code tags with the square brackets but it still looks like garbage next to your lines.

dependent validation lists fire worksheet change event

ajm

Well-known Member

Excel Facts

repairman615

Well-known Member

ajm

Well-known Member

ajm

Well-known Member

ajm

Well-known Member

repairman615

Well-known Member

ajm

Well-known Member

repairman615

Well-known Member

ajm

Well-known Member

ajm

Well-known Member

Similar threads

Forum statistics

Share this page

dependent validation lists fire worksheet change event

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock