dependent validation lists fire worksheet change event

[ajm]

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:

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.

<!-- Please do not remove this header -->
<!-- Table easily created from Excel with ASAP Utilities (http://www.asap-utilities.com) -->
<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"><font face="Tahoma" size="2" color="#000000"> </font></th><th bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></th><th bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></th><th bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></th></tr>
<tr><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Master Account Manager</font></td><td bgcolor="#FFFFFF" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>UNALLOCATED</b></font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td></tr>
<tr><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Select Customer Group</font></td><td bgcolor="#FFFFFF" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">AKSU KEBAB</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td></tr>
<tr><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Select Customer Account</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td></tr>
<tr><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></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]

Thats great!

Maybe try and incorporate a worksheet change event also for automation.

Something that determines which of the two macros to use.

Possibly like:

private sub worksheet_change
if (logical test) then
call ChooseMaM
else
call ChooseAccount
end if
end sub



The VB html maker from Richard Schollar will allow you to convert code to html and post like you have seen.

https://skydrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

He also has a html maker for posting data from worksheets to the board.
I recommend them both.

 

[ajm]

Thats great!

Maybe try and incorporate a worksheet change event also for automation.

i am not sure how much more i need. the first combo triggers the macro to select the account manager, and then clears the second combo and the Debtor filter in the pivot tables. so shows all customers for that account manager. Exactly what I wanted.

second combo then allows user to pick particular customers for the selected account manager. once again, what i wanted.

The VB html maker from Richard Schollar will allow you to convert code to html and post like you have seen.

https://skydrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

He also has a html maker for posting data from worksheets to the board.
I recommend them both.

sweet. thanks for that. i have been using ASAP Utilities to post screen shots and just manually wrapping code in tags. I will have a look at the link right now and you should see my posts change from now on. cool.

thanks for looking into it with me. i really enjoy seeing the way others do things and get an added kick when i manage to solve my own problem in the process.

cheers,

ajm

PS i will upload the file with the worksheet change event code to box.net for you to play with, if your interest is piqued.

 

[ajm]

Jeff, i have discovered a good sized flaw in my code. in attempting to speed it up, i added to both macros " PT.Manualupdate =True" and then False at the end of each. however - the code errors out with a run time error 91 - to do with the setting of the Object Variable or With Block variable. shall we keep going in this thread or does this warrant a new thread? I can never tell when to start a new one in similar circumstances.

 

[repairman615]

Hello,

I am thinking this would be a good time to post another question and as a bonus, if you have already installed the VhtmlMaker, then you get to try that out too!