Thread
:
Validation fields linked to pivot tables on separate worksheets
View Single Post
#
2
Posted to microsoft.public.excel.programming
Debra Dalgleish
external usenet poster
Posts: 2,979
Validation fields linked to pivot tables on separate worksheets
Something like this:
'===============================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim strField As String
On Error Resume Next
Select Case Target.Address
Case Range("C6").Address
strField = "Sales Zone"
Case Range("C7").Address
strField = "Market Area"
Case Range("C8").Address
strField = "Centre name"
Case Range("C9").Address
strField = "Centre No"
Case Else
GoTo exitHandler
End Select
Application.EnableEvents = False
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
With pt.PageFields(strField)
For Each pi In .PivotItems
If pi.Value = Target.Value Then
.CurrentPage = Target.Value
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
Next pt
Next ws
exitHandler:
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub
End Sub
'==============================
wrote:
Hi all,
Can anyone please help. I have a very basic knowledge of
VB
in Excel
and have found a piece of code to change any pivot table in my
workbook to match my validation drop down box. The problem is that I
wish to expand this to use a further 3 validation (drop down boxes).
Can anyone show me the extra
VB
code I would need?
The current working validation box is located in "Sales zone" in cell
C6
The additional fields I want to use are located in cells:
"Market Area" in cell C7
"Centre name" in cell C8
"Centre No" in cell C9
The Current
VB
code for working one drop down validation box is:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim strField As String
strField = "Sales zone"
On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False
If Target.Address = Range("C6").Address Then
For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
With pt.PageFields(strField)
For Each pi In .PivotItems
If pi.Value = Target.Value Then
.CurrentPage = Target.Value
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
Next pt
Next ws
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog:
http://blog.contextures.com
Reply With Quote
Debra Dalgleish
View Public Profile
Find all posts by Debra Dalgleish