run a macro in a protected sheet
 

I use Excel 2002

I created a workbook of 12 sheets, and potected each sheet with a password
with Autofilter enabled.

To Show all Data after filtering, I created a button on each sheet with a
command Click event as follows:
If ActiveSheet.FilterMode = True Then
ActiveSheet.ShowAllData
Else
Exit Sub
End If

When the button is clicked I got an error "ActiveSheet.ShowAllData"
highlighted.

I couldn't figure out what is the error.

Any help would be much appreciated






--
Microsoft Biased

run a macro in a protected sheet
 

You've protected worksheets so they can't be changed and now you're running a
macro to change the worksheet. Without first unprotecting the worksheet, you
can't change the worksheet.
HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Nora Najem" wrote:


I use Excel 2002

I created a workbook of 12 sheets, and potected each sheet with a password
with Autofilter enabled.

To Show all Data after filtering, I created a button on each sheet with a
command Click event as follows:
If ActiveSheet.FilterMode = True Then
ActiveSheet.ShowAllData
Else
Exit Sub
End If

When the button is clicked I got an error "ActiveSheet.ShowAllData"
highlighted.

I couldn't figure out what is the error.

Any help would be much appreciated






--
Microsoft Biased

run a macro in a protected sheet
 

Nora Najem wrote:
I use Excel 2002

I created a workbook of 12 sheets, and potected each sheet with a password
with Autofilter enabled.

To Show all Data after filtering, I created a button on each sheet with a
command Click event as follows:
If ActiveSheet.FilterMode = True Then
ActiveSheet.ShowAllData
Else
Exit Sub
End If

When the button is clicked I got an error "ActiveSheet.ShowAllData"
highlighted.

I couldn't figure out what is the error.

Any help would be much appreciated


I had the same problem and figured out that if I applied a custom
filter with blank criteria, XL would show all of the data. I don't know
why the standard "Show All" doesn't work in this situation. I imagine
one of the experts here might know. If you want a copy of that macro,
let me know here and I'll post it.

run a macro in a protected sheet
 

Dear "davegb"...

Thank you for your reply, In fact I do not know how to use custom filter. I
would be delighted if you supply me with the code.

I tried the following solution.

I used this AutoOpen macro:

Private Sub Workbook_Open()
Dim wSheet As Worksheet
For Each wSheet In Worksheets
wSheet.EnableAutoFilter = True
wSheet.Protect contents:=True, UserInterFaceOnly:=True
wSheet.Protect Password:="***"
Next wSheet
End Sub

This solution worked but it has a drawback which is when the workbook opens,
an unprotect messages will popup as many as the number of sheets.

My question is How to disable these popup messages in the same code ?.

Another drawback also is that when I unprotect a single sheet and, either
protect it manualy or leave it unproteced, then save and reopen the workbook
the AutoFilter will stop functioning unless I unproteced all the sheets and
close the workbook and open it again then I'll gain the functionality of
Autofiltering.

Your help would be much appreciated

Nora

Thank you again

--
Microsoft Biased


"davegb" wrote:


Nora Najem wrote:
I use Excel 2002

I created a workbook of 12 sheets, and potected each sheet with a password
with Autofilter enabled.

To Show all Data after filtering, I created a button on each sheet with a
command Click event as follows:
If ActiveSheet.FilterMode = True Then
ActiveSheet.ShowAllData
Else
Exit Sub
End If

When the button is clicked I got an error "ActiveSheet.ShowAllData"
highlighted.

I couldn't figure out what is the error.

Any help would be much appreciated


I had the same problem and figured out that if I applied a custom
filter with blank criteria, XL would show all of the data. I don't know
why the standard "Show All" doesn't work in this situation. I imagine
one of the experts here might know. If you want a copy of that macro,
let me know here and I'll post it.

run a macro in a protected sheet
 

Nora Najem wrote:
Dear "davegb"...

Thank you for your reply, In fact I do not know how to use custom filter. I
would be delighted if you supply me with the code.

I tried the following solution.

I used this AutoOpen macro:

Private Sub Workbook_Open()
Dim wSheet As Worksheet
For Each wSheet In Worksheets
wSheet.EnableAutoFilter = True
wSheet.Protect contents:=True, UserInterFaceOnly:=True
wSheet.Protect Password:="***"
Next wSheet
End Sub

This solution worked but it has a drawback which is when the workbook opens,
an unprotect messages will popup as many as the number of sheets.

What do you mean by "unprotect messages"?


My question is How to disable these popup messages in the same code ?.

Another drawback also is that when I unprotect a single sheet and, either
protect it manualy or leave it unproteced, then save and reopen the workbook
the AutoFilter will stop functioning unless I unproteced all the sheets and
close the workbook and open it again then I'll gain the functionality of
Autofiltering.

I don't understand that, but if we can fix the other problems, maybe
it'll take care of this one.


Your help would be much appreciated

Nora

Thank you again

Here is the code you requested:

Sub Showall()
Dim rStart As Range, rEnd As Range
Dim lRow As Long, lCol As Long

Set rStart = Range("B4")

lRow = rStart.End(xlDown).Row
lCol = Cells(lRow, Columns.Count). _
End(xlToLeft).Offset(, 2).Column

Set rEnd = Range(rStart, Cells(lRow, lCol))
rEnd.AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=wksMacRec.Range("S5:Z6"), Unique:=False

End Sub

In this case, the region to be filtered starts at B4, and in order to
determine the filter range, I had to go across row 4 and down column B.
You can remove whatever code you don't need. The last line is the
filter. It uses the range "S5:Z6" for the filter criteria. The column
titles are there, but the criteria, which would be in row 6, are blank.
For some reason, this worked when nothing else did for removing the
filter on a protected sheet.


--
Microsoft Biased


"davegb" wrote:


Nora Najem wrote:
I use Excel 2002

I created a workbook of 12 sheets, and potected each sheet with a password
with Autofilter enabled.

To Show all Data after filtering, I created a button on each sheet with a
command Click event as follows:
If ActiveSheet.FilterMode = True Then
ActiveSheet.ShowAllData
Else
Exit Sub
End If

When the button is clicked I got an error "ActiveSheet.ShowAllData"
highlighted.

I couldn't figure out what is the error.

Any help would be much appreciated


I had the same problem and figured out that if I applied a custom
filter with blank criteria, XL would show all of the data. I don't know
why the standard "Show All" doesn't work in this situation. I imagine
one of the experts here might know. If you want a copy of that macro,
let me know here and I'll post it.

run a macro in a protected sheet
 

My mistake. When I posted this, I had mis-read your reply and thought
you said you knew how to apply a custom filter. After I posted, I
noticed you said you didn't. It's pretty easy. Normally, you just copy
your column headers to another place on the worksheet, or to a
different sheet entirely. Then you put the filter criteria in the rows
immediately beneath the headers in the criteria range. Then do a "Data,
Filter, Custon Filter". This brings up the custom filter dialog box.
You can choose to filter the list where it is, or extract the filtered
result to another worksheet. You tell it where the list is, and where
the criteria are. Then click Ok.
For instance, if I have a database with columns for name, street
address, city, zip, I would copy these column headers to some other
location on the sheet. In the macro above, they've been copied to row
5, columns S through Z. If I wanted to filter for only people in zip
code 80203, I'd enter 80203 into the cell under the label Zip (or
Zip_Code. No spaces in criteria names). This is the range you select to
tell the custom filter where the criteria are.
In our situation, you want to show all the records, so you just leave
row 6 blank, so you only have the names of the fields, but no filter
criteria beneath them. This causes XL to show all the records.
Hopet this helps in your world.
Dave

run a macro in a protected sheet
 

Dear Dave

Thank you for your detailed reply, I really appreciate it

My file is a timesheet, where I put clients names, IDs and tasks in columns
A, B and C. The AutoFilter funcionality allow users to use the DROP DOWN
ARROW to choose clients etc... (I don't know if advanced or Custom filters
can do this).

The code I listed in my message worked fine, but I need to disable the
unprotect sheet prompts (the one you get when you try to unprotect a sheet)
every time I open the file. what I mean is that the users shouldn't bother to
get this message every time they open the timesheet, I alone should
unprotect the sheets to make certain changes or add sheets.

Thanks again
--
Microsoft Biased


"davegb" wrote:

My mistake. When I posted this, I had mis-read your reply and thought
you said you knew how to apply a custom filter. After I posted, I
noticed you said you didn't. It's pretty easy. Normally, you just copy
your column headers to another place on the worksheet, or to a
different sheet entirely. Then you put the filter criteria in the rows
immediately beneath the headers in the criteria range. Then do a "Data,
Filter, Custon Filter". This brings up the custom filter dialog box.
You can choose to filter the list where it is, or extract the filtered
result to another worksheet. You tell it where the list is, and where
the criteria are. Then click Ok.
For instance, if I have a database with columns for name, street
address, city, zip, I would copy these column headers to some other
location on the sheet. In the macro above, they've been copied to row
5, columns S through Z. If I wanted to filter for only people in zip
code 80203, I'd enter 80203 into the cell under the label Zip (or
Zip_Code. No spaces in criteria names). This is the range you select to
tell the custom filter where the criteria are.
In our situation, you want to show all the records, so you just leave
row 6 blank, so you only have the names of the fields, but no filter
criteria beneath them. This causes XL to show all the records.
Hopet this helps in your world.
Dave

run a macro in a protected sheet
 

Nora Najem wrote:
Dear Dave

Thank you for your detailed reply, I really appreciate it

My file is a timesheet, where I put clients names, IDs and tasks in columns
A, B and C. The AutoFilter funcionality allow users to use the DROP DOWN
ARROW to choose clients etc... (I don't know if advanced or Custom filters
can do this).

I didn't mean to replace what you are doing with AutoFilter with Custom
Filter. A Custom Filter can't do what you described. What I was saying
is that to remove a filter from a protected sheet, you use the Custom
Filter, which is what my macro does. Keep using the Auto Filter as you
are.


The code I listed in my message worked fine, but I need to disable the
unprotect sheet prompts (the one you get when you try to unprotect a sheet)
every time I open the file. what I mean is that the users shouldn't bother to
get this message every time they open the timesheet, I alone should
unprotect the sheets to make certain changes or add sheets.

I'm working on this on one of my worksheets, and will have it figured
out sometime today. If no one else gets back to you with a solution in
the meantime, I'll post it late today or early tommorrow.


Thanks again
--
Microsoft Biased


"davegb" wrote:

My mistake. When I posted this, I had mis-read your reply and thought
you said you knew how to apply a custom filter. After I posted, I
noticed you said you didn't. It's pretty easy. Normally, you just copy
your column headers to another place on the worksheet, or to a
different sheet entirely. Then you put the filter criteria in the rows
immediately beneath the headers in the criteria range. Then do a "Data,
Filter, Custon Filter". This brings up the custom filter dialog box.
You can choose to filter the list where it is, or extract the filtered
result to another worksheet. You tell it where the list is, and where
the criteria are. Then click Ok.
For instance, if I have a database with columns for name, street
address, city, zip, I would copy these column headers to some other
location on the sheet. In the macro above, they've been copied to row
5, columns S through Z. If I wanted to filter for only people in zip
code 80203, I'd enter 80203 into the cell under the label Zip (or
Zip_Code. No spaces in criteria names). This is the range you select to
tell the custom filter where the criteria are.
In our situation, you want to show all the records, so you just leave
row 6 blank, so you only have the names of the fields, but no filter
criteria beneath them. This causes XL to show all the records.
Hopet this helps in your world.
Dave