auto filter protection problems.

[maclav]

Thanks to those that helped with my previous problem however have a new one.
The data in the sheets that the autofilter is attached to has to be protected. certain rows of data are unprotected depending on which user is logged on. the problem i have is that i cant select anything from the list while the sheet is protected. i have unprotected the row the drop downs appear on but this did not help.
Any ideas
Thanks

 

[xlbo]

unprotect sheet
use autofilter
re-protect sheet

et voila

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[maclav]

non et viola.
The sheet is for users who can not/should not unprotect sheets. It holds financial information and if the auditors find they can change other peoples data then the company is in the deep and sticky.
They want to be able to using the autofilter while the sheet is protected.

 

[maclav]

as further clerification.
I have found a method that only works when the spread sheet is opened. however in order for the sheet to update the sql server i need to take the filtering off and then put it back on again.

 

[xlbo]

Well, you have posted in the VBA forum so I assumed you wanted to use autofilter via code....

If not, what version of excel ?

if 2003, you have an option to allow autofilter on protected sheets.

If < 2003, you're gonna struggle to allow users to use autofilter

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[maclav]

Do you understand what i am asking?
I have put on the auto filter using vba.
I have protected the rows that need to be locked using vba.
The users need to be able to use the filter without unprotecting the sheet.
So I am assuming that some where there is something like:
activesheet.autofilter.protect = false
what i need to know is the vba that acheives this end.
Is there any part of that i can make more clear for you?

 

[xlbo]

I understand perfectly thank you very much - what do YOU not understand?

In excel 2003 and above, there is a protection option that allows users to utilise autofilter when sheet protection is on. To see the argument where you set this, look in the help file for "protection"

If you are using an earlier version, you can use the "UserInterface" option of protection (I assume this is what you are referring to when you mention about "when the spreadsheet is opened". In which case, you need to set this in the "Workbook OPEN" event. Again, the help file will give you the syntax



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[maclav]

I have done this however when filters are removed and put on again after the information has been sent to sql it re sets the protection. And as you say it can only be done in the workbook open event.
So using this method they would have to close and open the spread sheet every time the wanted to update the database.
Which as you can imagine will not be acceptqable to them.

 

[xlbo]

when filters are removed and put on again after the information has been sent to sql

How is this happening? code or manually?

If it is in code, you can unporotect/reprotect via code or re-set the "UserInterfaceOnly" option - note - it doesn't have to be set in the workbook open event. It is generally done so as it is easiest to do it there (it is the only event that must fire at least once before the users see data). In reality, it can be set in any line of code so if you have code that removes / resets the autofilter, you can re-apply the UserInterfaceOnly option there.

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[maclav]

Ahhh ha!!!
I have found it. Yes it is in the code.
The thing i found was not clear about which part of it allowed the filter to work.
Thanks