pivotitems in a multiuser environment

K

kanan K

Hi
I have the following VBA code in my auto_open macro
'==============================================================
uname = UserName() 'gets windows login i
If LCase(uname) = "user1" The
uname = "GRUMMAN,ANITA
ElseIf LCase(uname) = "user2" The
uname = "THAI ,HERB
ElseIf LCase(uname) = "user3" The
uname = "BOGGS,JIM E
ElseIf LCase(uname) = "user4" The
uname = "MAXA ,ROB C
End I

'MsgBox unam
Sheets("sheet4").Visible = Tru
Sheets("sheet4").Selec
Set pt = ActiveSheet.PivotTables(1
Set pf = pt.PivotFields("Series"
Application.ScreenUpdating = Fals
Application.DisplayAlerts = Fals
On Error Resume Nex
With pt.PivotFields("Series"
pf.AutoSort xlManual, pf.SourceNam
For Each pi In pf.PivotItem
pi.Visible = Fals
If pi.Value = uname The
pi.Visible = Tru
End I
Next p
pf.AutoSort xlAscending, pf.SourceNam
End Wit
Application.DisplayAlerts = Tru
Application.ScreenUpdating = Tru
Sheets("sheet4").Visible = Fals
'============================================================

The issue is : when user1 opens this book (first user to open this book), it looks at 'GRUMMAN, ANITA'. When user2 opens this book, user2 should only look at 'THAI, HERB' , but it also brings up GRUMMAN,ANITA. But the code works OK, if I mad
user2 to look at any name that is alphabetically before the GRUMMAN, ANITA name in the sequence or list of pivot items ...
like any name staring with F,E,D,C,B or A.
I would really appreciate your input on this

thank
Kana
 
K

Kanan

This is just FYI: I got this problem resolved by setting all the pivotitems values to true using 'For each' statemen
and then using another 'for each' statment , set all pivotitems visible property to true except the specific item for that use
login id
=========================================
On Error Resume Nex
With pt.PivotFields("Series"
pf.AutoSort xlManual, pf.SourceNam
For Each pi In pf.PivotItem
pi.Visible = Tru

Next p
pf.AutoSort xlAscending, pf.SourceNam
End Wit

On Error Resume Nex
With pt.PivotFields("Series"
pf.AutoSort xlManual, pf.SourceNam
For Each pi In pf.PivotItem
pi.Visible = Fals
If pi.Value = uname The
pi.Visible = Tru
End I
Next p
pf.AutoSort xlAscending, pf.SourceNam
End Wit
==================================================
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top