Worksheet_Change(ByVal Target As Excel.Range)

[Guest]

I'm trying to alter multiple pivot tables and its working great as long as i
enter a number higher or equal to 100 in the target cell. I have to be able
to use numbers between 0 an 100 to. What am i doing wrong?

My code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim pt01 As PivotTable
Dim pf01 As PivotField
Dim pi01 As PivotItem

Dim pt02 As PivotTable
Dim pf02 As PivotField
Dim pi02 As PivotItem

Set pt01 = Sheets("Noter").PivotTables("3601-Fellesutgifter")
Set pt02 = Sheets("Saldobalanse").PivotTables("Saldobalanse")

Set pf01 = pt01.PivotFields("IK")
Set pf02 = pt02.PivotFields("IK")

If Target.Address = "$A$2" Then

For Each pi01 In pf01.PivotItems
If pi01 = Target.Value Then
pf01.CurrentPage = Target.Value
Exit For
End If
Next pi01

For Each pi02 In pf02.PivotItems
If pi02 = Target.Value Then
pf02.CurrentPage = Target.Value
Exit For
End If
Next pi02

End If

End Sub

I realy hope you can help!

 

[keepITcool]

your event handler changes values thus triggering more events that it
needs to handle..

try like:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
application.enableevents=False
'your code
application.enableevents=True
end sub



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Daggi wrote :

 

[Guest]

I'm sorry! It did not solve the problem.
The pivot tables have to change when target.value is between 0 and 700.
It's working when the value is between 100 and 700. Not when its 99 or less.

 

[keepITcool]

hmm. not reading very well, was I

note that the PivotItem's value is a string.
a small rewrite..
hopefully this works, else mail me the workbook.
(email in signature.. just add @ and .


Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim pt01 As PivotTable
Dim pf01 As PivotField
Dim pi01 As PivotItem

Dim pt02 As PivotTable
Dim pf02 As PivotField
Dim pi02 As PivotItem

'moved IF above the assignment of objects
If Target.Address = "$A$2" Then

Set pt01 = Sheets("Noter").PivotTables("3601-Fellesutgifter")
Set pt02 = Sheets("Saldobalanse").PivotTables("Saldobalanse")

'using pagefields not pivot fields
Set pf01 = pt01.PageFields("IK")
Set pf02 = pt02.PageFields("IK")

For Each pi01 In pf01.PivotItems
'using string compare
'and qualified properties iso relying on "default"
If StrComp(pi01.Value, Target.Value, vbTextCompare) = 0 Then
'avoid problems.. use the pi to set the pf. (not target)

pf01.CurrentPage = pi01.Value
Exit For
End If
Next pi01

For Each pi02 In pf02.PivotItems
If StrComp(pi02.Value, Target.Value, vbTextCompare) = 0 Then
pf02.CurrentPage = pi02.Value
Exit For
End If
Next pi02

End If

End Sub






--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Daggi wrote :