Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation fields linked to pivot tables on separate worksheets
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation fields linked to pivot tables on separate worksheets
On 23 Oct, 02:31, Debra Dalgleish wrote:
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 Contextureswww.contextures.com/tiptech.html Blog:http://blog.contextures.com Thanks Debra!!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Update linked pivot tables when page changes in main pivot | Excel Discussion (Misc queries) | |||
Is there a way to reference 2 separate worksheets with validation | Excel Worksheet Functions | |||
Linked Cells on Separate Worksheets | Excel Discussion (Misc queries) | |||
row fields in pivot tables | Excel Discussion (Misc queries) | |||
Validation Tables For Drop Down list on a Separate Worksheet. | Excel Worksheet Functions |