S
Santhosh Mani
Please find below a code which I recieved from this discussion forum and used
with Excel 2003. This code is used to update a pivot page field by entering
required value in a particular cell (Cell F4 in the below code) and double
click on it. This code is working perfect with Excel 2003 but when I convert
the same file into Excel 2007 and try to use it, I get debug screen pointed
to the line - SubNum.CurrentPage = Pi.Value.
Can someone help with this issue and make this code work under Excel 2007.
Thanks in advance.. Santhosh
--------------------------------------------
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim SubNum As PivotField
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Target.Address = "$F$4" Then
Worksheets("StmtData").PivotTables("PivotTable1").PivotCache.Refresh
Set SubNum = Worksheets("StmtData").PivotTables _
("PivotTable1").PageFields( _
"SubNo")
For Each Pi In SubNum.PivotItems
If Pi.Value = Target.Text Then
SubNum.CurrentPage = Pi.Value
Exit For
End If
Next
End If
Worksheets("StmtUSD").PivotTables("PivotTable2").PivotCache.Refresh
End Sub
with Excel 2003. This code is used to update a pivot page field by entering
required value in a particular cell (Cell F4 in the below code) and double
click on it. This code is working perfect with Excel 2003 but when I convert
the same file into Excel 2007 and try to use it, I get debug screen pointed
to the line - SubNum.CurrentPage = Pi.Value.
Can someone help with this issue and make this code work under Excel 2007.
Thanks in advance.. Santhosh
--------------------------------------------
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim SubNum As PivotField
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Target.Address = "$F$4" Then
Worksheets("StmtData").PivotTables("PivotTable1").PivotCache.Refresh
Set SubNum = Worksheets("StmtData").PivotTables _
("PivotTable1").PageFields( _
"SubNo")
For Each Pi In SubNum.PivotItems
If Pi.Value = Target.Text Then
SubNum.CurrentPage = Pi.Value
Exit For
End If
Next
End If
Worksheets("StmtUSD").PivotTables("PivotTable2").PivotCache.Refresh
End Sub