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?

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" wrote:

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?

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 he
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" wrote:

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" wrote:

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?

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
user won't have to deal with running the macro manually.


"RS" wrote:

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 he
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" wrote:

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" wrote:

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?

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.

"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
user won't have to deal with running the macro manually.


"RS" wrote:

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 he
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" wrote:

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" wrote:

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?

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
user won't have to deal with running the macro manually.


"RS" wrote:

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 he
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" wrote:

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" wrote:

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?

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
user won't have to deal with running the macro manually.


"RS" wrote:

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 he
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" wrote:

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" wrote:

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?