Sorting macro needed on a protected sheet

[RS]

Hi everyone. I saw one of the threads in the Excel worksheet functions
section that suggested that any macro or vba questions should be posted here.
So here goes...

Sorry if some of you have seen this quesiton in the other forum, but I've
gotten partial answers but no one has posted a solution (code) that I could
copy into my worksheet. I've never done vb coding before, so that's why I'm
seeking the assistance of the experts here. Here was my post:

I know that Excel 2000 disables the autofilter function when
the worksheet is protected and hence copied and modified a macro into
"ThisWorkbook" that would fix this. However, I also just read that the
sorting function is also disabled in Excel 2000. So my question is...how
would I modify my existing macro (or add a new macro) to also allow me to
sort? Here is the macro that I have in "ThisWorkbook":

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

Some of the comments I got were as follows: Gord Dibben wrote: "Most
everybody winds up with code to unprotect, sort, then re-protect."

Since I would need the macro to account for increasing numbers of rows as
additional info is added to the spreadsheet, Bob Phillips wrote: "You can
calculate the range like so

Set rng = Range(Range("A1"),Range("A1").End(xlDown))

Since I don't know vb coding, how would I incorporate this into the existing
macro? Would I need to paste a new macro code into the spreadsheet? If so,
what would the code be? I guess that I would replace A1 with A49 since
that's where the sorted data begins.

I'm assuming based on Gord's comments, that if the macro has to unprotect,
sort, and reprotect, that the person clicking on the macro won't be able to
see what the password is? Am I right in this assumption?

 

[JLatham]

Assuming you want to do all of this within the same code segment, you need
to get the worksheet unprotected first so all of this would go inside of the
With ... End With section.

But just ahead of the With Worksheets("Worksheet") statement put this:
Dim rng as Object

now inside of the With... to unprotect the sheet:
..Unprotect Password:="temp"

So do the sort, use Bob Phillips' suggestion as:

Set rng(Range("A1"),Range("A1").End(xlDown)

and this will do the sort using the setting of rng

With rng
.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With

Hope this helps. You may need to change the sort range to start at A2?

As for the visibility of the password, no the user won't see it as the sheet
is being unprotected and put back into protected status. They could see it
if they later look at the VBA code module and know where to look. But that's
not a huge problem, since it is well known that 'cracking' workbook and
worksheet passwords is easily done with any number of readily available
tools. But they won't see it without going looking for it.

 

[RS]

Dear Jerry (JLatham) & other experts in this Excel Programming community,

Sorry for the delay in my reply but I've been away since I posted my
question. Anyway...I tried your suggestion and I'll paste what I came up
with below. First, here are some clarifications: I don't necessarily need
it within the same code segment, but since I've never done vb coding I
thought that's where it had to go. If it's easier to put into a different
segment, just let me know. That being said, here is what I tried in the This
Workbook code section (based on your comments):

Private Sub Workbook_Open()
'check for filter, turn on if none exists
Dim rng As Object
With Worksheets("Worksheet")
If Not .AutoFilterMode Then
..Range("A1").AutoFilter
End If
..EnableAutoFilter = True
..Unprotect Password:="temp"
Set rng(Range("A49"),Range("A49").End(xlDown)
With rng
..Sort Key1:=Range("A49"), Order1:=xlAscending, Header:=xlGuess,
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With
..Protect Password:="temp", _
Contents:=True, UserInterfaceOnly:=True
End With
End Sub

I didn't know if it mattered where I entered some of the items so let me
know if I need to change anything around. The reason I said A49 is because
that's where my data that I need sorted starts. However, when I open the
spreasheet I get a Syntax error with the following line highlighted:
Set rng(Range("A49"),Range("A49").End(xlDown)
Besides the sort still not working, the Autofilters now stopped working also.

When the Set rng line and .Sort line originally had A1 in them, I got a
Syntax error. When I changed the first line's A1's to A2's ("A2"), the
Compile error was: "Expected: list serparator or )". Changing the 2nd line
(the .Sort line) to A2 gave the error: Expected: named parameter.

I tried some other things to include in my sort criteria:
1) My sort data range starts in row 49, specifically A49:X49, and
2) I want to sort by column B.

Based on this I tried modifying the middle of the code as such:
Set rng(Range("A2"),Range("A49:X49").End(xlDown)
With rng
..Sort Key1:=Range("B49"), Order1:=xlAscending, Header:=xlGuess,
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With

However, when I open the spreadsheet I still get the syntax error with the
Set rng(Range("A2"),Range("A49:X49").End(xlDown) line highlighted.

DIFFERENT METHOD:
I also tried to attack this problem by creating a macro instead. I used the
spreadsheet which had the original Autofilter code only and then tried
recording 2 different macros. Summarizing this...the ThisWorkbook 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 1st macro I tried (found in Module2) was:
Sub Macro2()
' Macro2 Macro

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

The 2nd macro I tried involved highlighting the rows instead:
Sub testsort()
' testsort Macro

'
ActiveSheet.Unprotect "temp"
Rows("49:68").Select
Selection.Sort Key1:=Range("B49"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ActiveSheet.Protect "sctemp", DrawingObjects:=True, Contents:=True,
Scenarios:=True
End Sub

While both these macros work (in terms of sorting, at least), running them
disables the Autofilter function. Fixing this requires me to close & reopen
the spreadsheet. So...couple of issues here:
1) How would I modify the macro to account for increasing numbers of rows
as additional info is added to the spreadsheet (such as Bob had suggested).
2) How do I modify it to still allow Autofiltering?
3) Using this requires the person to run a macro, so would it be easier to
use this method or the original idea of a combined code (or separate code
appearing in Sheet1 or ThisWorkbook for example)?

Sorry for the extremely long reply, but I wanted to be as thorough as
possible to help anyone out there looking at this to get a clear picture of
what is going on. Any help by the Excel experts here in this community is
welcome. Thank you.

 

[JLatham]

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 (e-mail address removed)
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
user won't have to deal with running the macro manually.

 

[RS]

Dear Jerry,

Thanks for the reply. I will try making the changes you suggested and see
if that works and then post what happens tomorrow. In regards to "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" a couple of things
should be mentioned.

I only want the sorting to happen when a user wants it to happen.
Currently, with the Autofilter code only in the spreadsheet, the Autofilter
functionality is restored as if the sheet were unprotected and the user can
autofilter whenever they want to. Without the code, protecting the sheet
dislplays the autofilter arrows, but placing the cursor over it doesn't do
anything. I would like the sort to work that way also (by simplying going to
the sort menu), but if not, having some sort of button that would activate
the macro event would be fine also.

 

[RS]

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 "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 I’ve 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, wouldn’t this present a problem with the other
worksheets?

 

[JLatham]

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]

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]

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]

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]

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.

 

[JLatham]

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]

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

'
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 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 worksheet, 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 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?