Pivot - limiting user access with VBA

Hello,
Anyone have a good idea on how to limit user access to data in a Pivot
table. I have ONE pivot which I want to use as a basis for several users
(who should not see each others data).

My thought was to create a user access list (username, passw, access level
to pivot), and then dynamically filter the data in the pivot based on this
input.

Limitation -> Excel 2000.

Your best bet is to provide separate workbooks with just the data they
should see. any protection you might provide with excel is easily defeated.
any protection you provide with VBA is also easily defeated by disabling
macros.

--
Regards,
Tom Ogilvy

"Christian" <[email protected]> wrote in message
news:[email protected]...
> Hello,
> Anyone have a good idea on how to limit user access to data in a Pivot
> table. I have ONE pivot which I want to use as a basis for several users
> (who should not see each others data).
>
> My thought was to create a user access list (username, passw, access level
> to pivot), and then dynamically filter the data in the pivot based on this
> input.
>
> Limitation -> Excel 2000.
>

Thanks,
I guessed so. Could there be any ways to delete data from the cache, so that
when the pivot is generated (based on user input) - it does not contain any
other data than he/she should see?

You can get around VBA disabling of macros by using xlSheetVeryHidden - and
load the pivot only after user has entered password.

"Tom Ogilvy" wrote:

> Your best bet is to provide separate workbooks with just the data they
> should see. any protection you might provide with excel is easily defeated.
> any protection you provide with VBA is also easily defeated by disabling
> macros.
>
> --
> Regards,
> Tom Ogilvy
>
> "Christian" <[email protected]> wrote in message
> news:[email protected]...
> > Hello,
> > Anyone have a good idea on how to limit user access to data in a Pivot
> > table. I have ONE pivot which I want to use as a basis for several users
> > (who should not see each others data).
> >
> > My thought was to create a user access list (username, passw, access level
> > to pivot), and then dynamically filter the data in the pivot based on this
> > input.
> >
> > Limitation -> Excel 2000.
> >
>
>
>

No to the cache unless you want to rebuild it by taking the data out of the
source

> You can get around VBA disabling of macros by using xlSheetVeryHidden -
and
load the pivot only after user has entered password.

sorry, that just isn't true. I can show you code posted here that will break
the password for the workbook (it can be run from a separate workbook) and
then I can manually unhide your sheet. xlSheetVeryHidden is not a means of
protection. It is a means of hiding a sheet.

As long as you grant access to the workbook (and what is the point if they
can't access the workbook), they will have access to what is inside.
Perhaps you need to put your data on a database server where you can control
access by using VBA code to access the server and build your pivot table or
report.

--
Regards,
Tom Ogilvy

"Christian" <[email protected]> wrote in message
news:[email protected]...
> Thanks,
> I guessed so. Could there be any ways to delete data from the cache, so
that
> when the pivot is generated (based on user input) - it does not contain
any
> other data than he/she should see?
>
> You can get around VBA disabling of macros by using xlSheetVeryHidden -
and
> load the pivot only after user has entered password.
>
> "Tom Ogilvy" wrote:
>
> > Your best bet is to provide separate workbooks with just the data they
> > should see. any protection you might provide with excel is easily
defeated.
> > any protection you provide with VBA is also easily defeated by disabling
> > macros.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Christian" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hello,
> > > Anyone have a good idea on how to limit user access to data in a Pivot
> > > table. I have ONE pivot which I want to use as a basis for several
users
> > > (who should not see each others data).
> > >
> > > My thought was to create a user access list (username, passw, access
level
> > > to pivot), and then dynamically filter the data in the pivot based on
this
> > > input.
> > >
> > > Limitation -> Excel 2000.
> > >
> >
> >
> >