using macro to change auto filter in shared workbook

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

wish - 25 Jan 2006 07:49 GMT

I am using excel 2003 with a shared workbook. With a macro i enter info into
a userform to place data into a filtered list, the sheet is protected.
This works fine until the macro tries to change the auto filter to display
the new entry. The macro fails because the sheet is protected, while trying
to use the auto filter. When I protect the sheet i have the "allow use of
auto filter" box checked.
I cannot unprotect the document as it is shared, I also need to protect
formulas in the worksheet. It will allow me to change the auto filter
manually, however I am trying to do this with a macro. Any ideas???

Reply to this Message

Tom Ogilvy - 25 Jan 2006 14:51 GMT

I think you have to protect the workbook with UserInterFaceOnly set to True
to adjust the filter, but I don't believe you can change protection settings
in a protected workbook and you can only set this with code each time the
workbook is opened, so what you want to do may not be possible.

Signature

Regards.
Tom Ogilvy

> I am using excel 2003 with a shared workbook. With a macro i enter info into
> a userform to place data into a filtered list, the sheet is protected.
[quoted text clipped - 5 lines]
> formulas in the worksheet. It will allow me to change the auto filter
> manually, however I am trying to do this with a macro. Any ideas???

Reply to this Message