RE: Sorting macro needed on a protected ***

Thanks for the heads up about the DataOption code. I’m waiting to find out
if any of the people who’ll be using my spread*** have Excel 2000, or if
they all have Excel 2003, before reposting this thread as a new discussion to
try and fix the Autofilter issue (great idea). In the meantime…

Excel 2000 update (when working with this version):
I found that using the 1st macro from my 11/28 post (testsortm4 Macro) did
the 2 criteria sort (without needing to turn the Autofilter off & back on in
the code). Since I didn’t have any hidden rows in the sort range, I didn’t
need to worry about turning the filter off first.

Coding tip for other users (with the *** already protected with a
password). If your code has the password on a different line, like this:
Active***.Unprotect
Unprotect Password:="temp"
you will still be prompted to enter a password when running your macro even
though it’s in your code. To fix this, change your code & put the password
in quotes on the first line and delete the 2nd line, like this:
Active***.Unprotect "temp”. Although…I found that clicking “Cancel” or
closing the password dialog box performed the sort macro anyway without
entering the password (& the *** was still protected).

Regarding the comments in the last 2 paragraphs of the 2nd prior post
(11/29/2006 5:17 AM PST), “I was curious when you said it would work again if
you closed the workbook and then reopened it. I tried that here and while the
autofilter buttons were still visible on the ***, they remained inactive
while my *** was in the protected state.” To fix this, you need the
Autofilter code added into your ThisWorkbook.
Private Sub Workbook_Activate()
'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’ve found a couple of ways to trick Excel 2000 into restoring the
Autofilters after performing this sort on a protected ***. The first is to
simply close and reopen the file. ALTERNATIVELY, if there are at least 2
Excel files that are open (won’t work with just 1), & because the first line
of the code was changed from Private Sub Workbook_Open() to Private Sub
Workbook_Activate(), Autofiltering can be restored without having to close &
reopen the file. Two ways, either by:
1) minimizing & restoring (or maximizing) the file’s window (use the
minimize window icon located on the silver bar; won’t work if you minimize
the whole application by clicking the minimize icon on the blue bar), or,
2) EVEN EASIER, going to the other file (by clicking on it or pressing CTL
TAB) and returning back to the original file.

Unless someone else knows an easier way to restore Autofiltering after
running this macro in Excel 2000, this is the only workaround I can think of.
I’ll try your suggestions on a computer with Excel 2003 and then post my
results here.


"JLatham" wrote:

If you've really got to find out how to possibly make this work in 2000, you
may want to start another discussion in this forum. I see this is now back
on page 65 and only you and I are exchanging ideas. I've noticed that things
seem to get 'forgotten' or ignored when they're way back in the list. It may
be that someone else who actually has a solution for you isn't seeing the
discussion now.

By the way, a "head's up" should you get into Excel 2003 and record a macro
to do the sorting. If you look at the code generated you will see an added
parameter in the .Sort command: DataOption1:=xlSortNormal
There will be a DataOption#, where # is 1, 2 or 3, for each sort key you
chose. If you try to run that .Sort routine in Excel 2000, it will fail.
Again it is a case of the parameter not existing in Excel 2000. You can
safely delete all of the DataOption#:= entries in the code if you think it
may be used in both Excel 2000 and Excel 2003.

"RS" wrote:

Knowing the Excel 2000 limitation, 2 sets of questions come to mind. First,
In Excel 2003 (I guess we can’t 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

'
Active***.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
Active***.Protect "temp", DrawingObjects:=True, Contents:=True,
Scenarios:=True
End Sub

2. From 11/27 post:

Sub Macro1127()
' Macro1127 Macro

'
Active***.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
Active***.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 don’t 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 work***, the
Activate event occurs. So, if there’s 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 Work*** 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 *** 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 *** 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 work*** 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

'
Active***.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
Active***.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 spread***. 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:
Active***.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 work*** 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 work*** 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 *** is put into
protected state.
A complete statement would look like this
Active***.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 *** protection
Turn off any autofiltering in effect
Determine the sort parameters (range, sort keys)
Do the sort
Turn AutoFiltering back on
Put the *** back into protected state

If you put the button to perform this operation/call the macro on the ***
that the work is to be done on, then Active***. 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 spread*** have anthing to do with appointment
schedules or have a *** named "Monday Intake Log"....weird. Although if
their problem was similar to mine, it might make it easier to fix my
spread***.

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 spread***, 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 spread***. However, we seem to be
close to a solution, so rather than troubling you with having to decipher the
rest of my spread***, 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 work*** 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 *** '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 (don’t 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 "Work***" tab opening
first, it automatically sorts the data

New observations: opening the workbook with another tab opening first (not
the "Work***" tab where the relevant data is), results in a Run-time error
‘1004’: Sort method of Range class failed. Also, since I want the work***
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 “Work***” tab.

.