p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Excel VBA (http://p2p.wrox.com/forumdisplay.php?f=79)
-   -   Multiple Targets in one Worksheet (http://p2p.wrox.com/showthread.php?t=95062)

tnshah October 1st, 2015 02:51 PM

Multiple Targets in one Worksheet
 
I wrote the below code, however:

Code:

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 = "Geography"

On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False

    If Target.Address = Range("B18").Address Then
 

        Set ws = ThisWorkbook.Worksheets("DataPivotCategory")
            Set pt = ws.PivotTables("PivotTable3")
                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
End If
   

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

I need to do multiple changes in this worksheet; i.e. i also need Cell B51 to be target value for a pivot item of a different pivot (per below). How do i do this if i can't have multiple target addresses in one module?

[code]
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 = "Geography"

On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False

If Target.Address = Range("B51").Address Then


Set ws = ThisWorkbook.Worksheets("DataPivotCategory")
Set pt = ws.PivotTables("PivotTable4")
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
End If


Application.EnableEvents = True
Application.ScreenUpdating = True


End Sub
[code/]

Zakalwe October 31st, 2015 10:06 AM

Multiple Targets in one Worksheet
 
Hi,

1. You are using the same code in both PivotTable3 & PivotTable4, after this: If Target.Address = Range("xxx").Address Then
Why not wrap this code in the function/Sub then call the same function twice, supplying the pivot table as a parameter for each call?

2. This is the same thing:
Code:

If Target.Address = Range("B18").Address Then
/
Code:

If Target.Address = Range("B51").Address Then
Select case Target.Address
Case "B18"
'Relevant Code
Case "B51"
'Relevant Code

Or ifThenElse.
If Target.Address = "B18" Then
'Relevant Code
ElseIf Target.Address = "B51"
'Relevant Code
Else
'Relevant Code
EndIf

On Worksheet Change the check the cell/Target that changed/triggered the Change event and execute relevant/unique steps applicable for that Target/TargetValue.

zweniryu@gmail.com


All times are GMT -4. The time now is 12:48 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.