Knowing the Excel 2000 limitation, 2 sets of questions come to mind. First,
In Excel 2003 (I guess we cant know about Excel 2002), would the original
macro (from my 11/17 post) work and still retain the Autofiltering
capability, or would I need to use the newer macro (from my 11/27 post) where
I disabled & re-enabled Autofiltering. Here are those 2 macros:
1. From 11/17 post:

Sub testsortm4()
' testsortm4 Macro

'
ActiveSheet.Unprotect "temp"
Range("A49").Select
Range(Range("A49"), Range("X49").End(xlDown)).Select
Selection.Sort Key1:=Range("B49"), Order1:=xlAscending,
Key2:=Range("H49" _
), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
ActiveSheet.Protect "temp", DrawingObjects:=True, Contents:=True,
Scenarios:=True
End Sub

2. From 11/27 post:

Sub Macro1127()
' Macro1127 Macro

'
ActiveSheet.Unprotect "temp"
Selection.AutoFilter
Range("A48").Select
Range(Range("A48"), Range("X48").End(xlDown)).Select
Selection.Sort Key1:=Range("B48"), Order1:=xlAscending,
Key2:=Range("H48" _
), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
Selection.AutoFilter
ActiveSheet.Protect "temp", DrawingObjects:=True, Contents:=True, _
Scenarios:=True
End Sub

If using the 2nd macro, does it work as is (in Excel 2003), or do I need to
change the Scenarios line to:
€œScenarios:=True, AllowFiltering:=True€�?

The second question from the 1st paragraph involves Excel 2000. Which macro
should I use, and would modifying that macro with some sort of Activate event
re-enable Autofiltering without me having to close & reopen the workbook?
The restriction would still need to be that I dont want the sorting to
happen unless the client activates the macro. You had mentioned that having
an Activate event means that whenever a person goes to that worksheet, the
Activate event occurs. So, if theres no straightforward code that will
restore Autofiltering, would I need to modify my macro to go to another tab
in the workbook and then back to the Worksheet to reactivate Autofiltering?

One other observation. In Excel 2000, how come when I manually unprotect,
sort, & then reprotect, the Autofilter still works, but if I run my macro
which does this, Autofiltering becomes disabled?


"JLatham" wrote:

I think I owe you an apology here. The actual solution, or key, to this
issue was in the first line of the 3rd paragraph of your initial post and it
didn't trigger the proper response from me.

The behavior you're having problems with are normal in Excel *2000*. That's
what's causing the Application Error 1004 when you add
, AllowFiltering:=True
to the protection statement. That feature does not exist in Excel 2000.
I'm not certain if it exists in Excel 2002 (XP) , but it does exist in Excel
2003. I've confirmed this by opening up a virtual machine with Office 2000
installed and verifying that while a sheet is protected, AutoFiltering is
truly not available.

So we've been chasing a ghost through this. We might have even chased it
longer if you hadn't mentioned the 1004 error coming up when you added the
extra parameter when putting the sheet back into protected status. That made
me go back and read to see what version of Excel you were using, and I found
it in that first post of yours.

"RS" wrote:

Since I want the worksheet to only sort when the user requests it, the
problem in my 3rd paragraph (from 11/17 post) no longer occurs since I had
removed the sort code from the Workbook_Open() section and used a macro to do
the sorting. Therefore, ThisWorkbook Autofilter code remains unchanged:
Private Sub Workbook_Open()
'check for filter, turn on if none exists
With Worksheets("Worksheet")
If Not .AutoFilterMode Then
.Range("A1").AutoFilter
End If
.EnableAutoFilter = True
.Protect Password:="temp", _
Contents:=True, UserInterfaceOnly:=True
End With
End Sub

I recorded a new macro - turning off protection & autofiltering, performing
the 2 level sort with a header row, and then re-enabling it at the end, as
you had suggested. Here is the code:
Sub Macro1127()
' Macro1127 Macro

'
ActiveSheet.Unprotect "temp"
Selection.AutoFilter
Range("A48").Select
Range(Range("A48"), Range("X48").End(xlDown)).Select
Selection.Sort Key1:=Range("B48"), Order1:=xlAscending,
Key2:=Range("H48" _
), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
Selection.AutoFilter
ActiveSheet.Protect "temp", DrawingObjects:=True, Contents:=True, _
Scenarios:=True
End Sub

However, I still have the same problem of the macro disabling the
Autofiltering, unless I close and reopen the spreadsheet. I even tried
adding the AllowFiltering:=True at the end of the Scenarios line as you
suggested:
Scenarios:=True, AllowFiltering:=True
This results in a Run-time error €˜1004: Application-defined or
object-defined error. Clicking debug then highlights the modified code below
in yellow:
ActiveSheet.Protect "sctemp", DrawingObjects:=True, Contents:=True, _
Scenarios:=True, AllowFiltering:=True

How do I fix this? Aside from removing the AllowFiltering:=True part. Do I
need to simply change the first line of the Autofilter code (located in
ThisWorkbook) from Private Sub Workbook_Open() to Private Sub
Worksheet_Activate()? Although from you description, it sounds like the user
would need to navigate away from the worksheet and then back to it in order
to keep the Autofilter active. How would I avoid this and keep the
Autofilter active all the time?

"JLatham" wrote:

The problem described in the 3rd paragraph is caused by the worksheet being
protected. If you look in the Workbook_Open() event, you never unprotect it,
although it gets protected later on, just before the end of the With segment.
Being still protected at that point, the attempt to set things like
.AutoFilter failed.

I found in working with the other person's problem that even though your
.Protect statement may contain the parameter:
AllowFiltering:=True
that it only seems to allow it if it is in use when the sheet is put into
protected state.
A complete statement would look like this
ActiveSheet.Protect Password:="pwrd", DrawingObjects:=True, Contents:=True, _
Scenarios:=True, AllowFiltering:=True


I think your second macro will generally work (as long as the ranges are set
as you need them to be) and you remember to add code turn filtering back on
just before putting it back into protected state.

Your code logic needs to generally be like this pseudo-code:
Turn off sheet protection
Turn off any autofiltering in effect
Determine the sort parameters (range, sort keys)
Do the sort
Turn AutoFiltering back on
Put the sheet back into protected state

If you put the button to perform this operation/call the macro on the sheet
that the work is to be done on, then ActiveSheet. is an appropriate reference
to use.


"RS" wrote:

Dear Jerry,

Hi there, hope you had a good Thanksgiving. First off, let me say
"AAAAuuuuugggghhhhh!!!!!!!". I just spent a lot of time composing my reply
to you, but when I tried to post it, I was told to re-enter my password, upon
which my reply had been deleted. So, here goes attempt #2 (the abridged
response).

It seems that someone did take advantage of the situation because I never
e-mailed you, nor does my spreadsheet have anthing to do with appointment
schedules or have a sheet named "Monday Intake Log"....weird. Although if
their problem was similar to mine, it might make it easier to fix my
spreadsheet.

From my post on 11/17, I had fixed the vb error by comparing my code with
the 2 new macros I had recorded and adding the _ to the end of the line.
Regarding the remaining issues with my spreadsheet, rather than retyping
everything, start at the 3rd paragraph of my 11/17 post, this should provide
the necessary info.

Thanks for the open offer to look at my spreadsheet. However, we seem to be
close to a solution, so rather than troubling you with having to decipher the
rest of my spreadsheet, I think we should be able to solve this in this forum
shortly (if not...then I can e-mail it to you). I see the discussions in
these forums as learning opportunities and a way for me to improve my Excel
skills so that I too, can help other people in the future with what I've
learned. Thanks again.

"JLatham" wrote:

Perhaps someone else pulled a sly one over on me? I was sent a workbook that
certainly had look and feel of what we'd been discussing here. I did a bunch
of work on it and sent it back and was told that what I'd done did the job.
And that workbook ended up using .End(xlUp) along with a sort that started at
A1. Naturally the email did not come from "RS" but they seldom do.

Your workbook have anything to do with the intake of appointment schedule
information, have a worksheet named "Monday Intake Log"? That is the one I
was referring to. I thought it was tied to this discussion. Funny as heck
if I fixed something I didn't know was broken - or was broken in a fashion
similar to the way yours is?

You mention that you get the error at the TWO lines, that both are in red.

Fix that by either going to the right end of the first line and use the
delete key to remove the line break between the two lines (making that all
one long line), or going to the end of that first line and make sure there's
one space following the last comma and then insert an underscore character so
that the lines look like:
.Sort Key1:=Range("B49"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

The space-underscore-carriage return tells VB that the line following is
also part of the current line.

If this doesn't help, or you are still having troubles, let me know and I'll
try to help more. Sorry for the confusion. The offer for me to look at it
remains open: send as attachment to email to (remove spaces) HelpFrom @
jlathamsite.com

Sorry for the confusion.

"RS" wrote:

Dear Jerry,

I see your response but don't understand it. The xlDown did work, the only
lingering problem was as mentioned above. The sort range did start in row 49
(not row 1). This is also where the data that needs to be Autofiltered also
is. I don't know if you were confusing me with someone else whom you helped,
but your response to my previous post would be greatly appreciated. Thanks!

"JLatham" wrote:

As an update: RS sent me the file and a revised "specification" for it all.
In the end we used .End(xlUP) to find the end of the range to be sorted,
pretty much did away with the need for the Select Case statements completely,
and set up the sort range reference properly to do what needed to be done.

The sort range actually began with row 1, with headers in it, and then
needed to go down the sheet 'crossing' barriers to .End(xlDown) caused by
gaps in the data to be sorted. Thus we went with .End(xlUP) to find out what
needed to be sorted.

"RS" wrote:

Dear Jerry,

I fixed the Set rng line, however, I now get a Syntax error with the first
line of the following code highlighted:
.Sort Key1:=Range("B49"), Order1:=xlAscending, Header:=xlGuess,
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

By the way, for both lines of code, the font color is red (dont know if
this simply means that this is the line that needs to be fixed). I compared
this section of the code with the code from the 2 macros I recorded (can
refer to my post above) and noticed that there was an underscore which was
missing after €œxlGuess,€� above. Changing it to xlGuess, _ fixed the Syntax
error. Now, when one opens the workbook with the "Worksheet" tab opening
first, it automatically sorts the data

New observations: opening the workbook with another tab opening first (not
the "Worksheet" tab where the relevant data is), results in a Run-time error
€˜1004: Sort method of Range class failed. Also, since I want the worksheet
to only sort when the user requests it, it seems to me that the easiest thing
to do will be to have the user run a macro located in the €œWorksheet€� tab.

Therefore, I did a "Save As" of my workbook and used the version where I
only have the Autofilter code present (as in the DIFFERENT METHOD section in
my previous post; the code was actually from Debra Dalgleish's Contextures
site). I then recorded a macro that did the same sort criteria as before
(column B), but also included a second sort criteria (column H). Summarizing
this...the ThisWorkbook Autofilter code was (same as originally unmodified
code):
Private Sub Workbook_Open()
'check for filter, turn on if none exists
With Worksheets("Worksheet")
If Not .AutoFilterMode Then
.Range("A1").AutoFilter
End If
.EnableAutoFilter = True
.Protect Password:="temp", _
Contents:=True, UserInterfaceOnly:=True
End With
End Sub

The new macro I tried (found in Module2) is:
Sub testsortm4()
' testsortm4 Macro

'
ActiveSheet.Unprotect "temp"
Range("A49").Select
Range(Range("A49"), Range("X49").End(xlDown)).Select
Selection.Sort Key1:=Range("B49"), Order1:=xlAscending,
Key2:=Range("H49" _
), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
ActiveSheet.Protect "temp", DrawingObjects:=True, Contents:=True,
Scenarios:=True
End Sub

So now that Ive decided to go the macro route, the only lingering problem I
have is that running the macro disables the Autofiltering, unless I close and
reopen the spreadsheet. How do I fix this? Do I need to simply change the
first line of the Autofilter code (located in ThisWorkbook) from Private Sub
Workbook_Open() to Private Sub Worksheet_Activate()? Although from you
description, it sounds like the user would need to navigate away from the
worksheet and then back to it in order to keep the Autofilter active. How
would I avoid this and keep the Autofilter active all the time?

Also, you said that €œBy being in the sheet's _Activate() event you don't
have to worry about being so specific with the worksheet name. You can use
ActiveSheet instead of Worksheets("WorksheetName") to reference the sheet.€�
Since I have 5 tabs, wouldnt this present a problem with the other
worksheets?


"JLatham" wrote:

First the syntax error, apparently my fault
Set rng = Range(Range("A49"),Range("X49").End(xlDown))
is the way it should be - I'll take the heat for that one, unless I can go
find the other discussion and lay it on Bob's doorstep <g.

Notice a couple of things about that; this should correct the syntax error,
also, I changed the second A49 to X49 so that it will pick up all of the area
to be sorted. With that done, you should be able to now use "B49" as the
Sort Key1 value.

As for making the sorting automatic, I presume you want this information to
be sorted (and auto-filtered) each time a user looks at it. I'd move the
code that was in the Workbook_Open() event (with the typo corrected) into
that worksheet's Worksheet_Activate() event to do that. That way the data
would get sorted each time the user goes to that worksheet. Being in the
Workbook_Open() event it gets run automatically, but only once.

By being in the sheet's _Activate() event you don't have to worry about
being so specific with the worksheet name. You can use ActiveSheet instead
of Worksheets("WorksheetName") to reference the sheet.

If this doesn't help or if you'd like me to look at it in more detail, you
could attach the workbook to an email and send it to
and I'll see if that gives me any more ideas. I'll have to freshen my mind
on how to deal with AutoFilter in the situation with a protected sheet. But
I think Bob's original idea was valid, and that with the typo corrected it
should work; and if you move it to the Worksheet_Activate() event then the