macro for sorting in protected sheet

G

Guest

Hi all

I created a button in a protected worksheet to allow sorting of data in
column A6 to A60 which does not work. Pls help.

I used the following code which I found in the discussion group. What I need
is to sort only a range of datas in one column, eg A6 to A60

Sub sortit()
Dim coltosort As Range
Set coltosort = Application.InputBox(Prompt:= _
"Select A Column", Type:=8)
ActiveSheet.Unprotect Password:="justme"
ActiveSheet.UsedRange.Sort Key1:=coltosort.Cells(2), _
Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
With ActiveSheet
...Protect Password:="justme"
...EnableSelection = xlNoRestrictions
End With
End Sub
 
G

Guest

Hi
In Xl2003 you can set the protection in unlocked cells to allow the user to
sort - so a macro will not be needed.

Peter
 
G

Guest

Ok, I think I see the 'why' of your question - the code you have sorts the
entire UsedRange and not the area A6:A60 which is all you want to sort. I
presume that in your real code you're using the correct password where
needed, so...
Sub sortit()
Dim coltosort As Range
Set coltosort = ActiveSheet.Range("A6:A60")
ActiveSheet.Unprotect Password:="justme"
coltosort.Select
Selection.Sort Key1:=coltosort.Cells(2), _
Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
With ActiveSheet
.Protect Password:="justme"
.EnableSelection = xlNoRestrictions
End With
End Sub

Note the change from .. to . for the interior of the With...End With
statements.
 
G

Guest

excel 2000 so no luck for that.

Billy Liddel said:
Hi
In Xl2003 you can set the protection in unlocked cells to allow the user to
sort - so a macro will not be needed.

Peter
 
G

Gord Dibben

Works fine for me.

Did you copy it exactly as Rick posted?


Gord Dibben MS Excel MVP
 
G

Gord Dibben

Try it again.

Go through line by line after copying and pasting to be sure it is "exactly" the
same.

Sub sortit()
Dim coltosort As Range
Set coltosort = ActiveSheet.Range("A6:A60")
ActiveSheet.Unprotect Password:="justme"
coltosort.Select
Selection.Sort Key1:=coltosort.Cells(2), _
Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
With ActiveSheet
.Protect Password:="justme"
.EnableSelection = xlNoRestrictions
End With
End Sub


Gord
 
G

Guest

As Gord said, go through it line by line and make sure your copy is exactly
the same. I actually tested this before posting it. Copy it all from the
Sub all the way down to the End Sub to make sure all changes were
incorporated.

If it errors again, hit the [Debug] option and see which line it halted on.
It will be highlighted in the VBA Editing window.

Most likely cause of Error 400 here would be that the sheet is not being
unprotected. Check that the password to unlock (Unprotect) your sheet is
justme spelled exactly like that. If it is not unprotected, the
coltosort.Select line will fail with that error - I know, I just helped
another person with exactly that type of problem (trying to use
Application.GoTo on a protected sheet: Error 400).
 
G

Guest

sorry for the trouble

I had tried again by copy n paste the code in two ways n the following occured

1. right-click worksheet tab n paste the code, run macro and error 400
with only option of OK or Help (worksheet with passwordword "justme")

2. right-click top right hand corner, view code, add module, paste macro
code.
when run, error n when debug,
selection.Sort to DataOption1 in yellow with a arrow pointing at
DataOption1

tnks



JLatham said:
As Gord said, go through it line by line and make sure your copy is exactly
the same. I actually tested this before posting it. Copy it all from the
Sub all the way down to the End Sub to make sure all changes were
incorporated.

If it errors again, hit the [Debug] option and see which line it halted on.
It will be highlighted in the VBA Editing window.

Most likely cause of Error 400 here would be that the sheet is not being
unprotected. Check that the password to unlock (Unprotect) your sheet is
justme spelled exactly like that. If it is not unprotected, the
coltosort.Select line will fail with that error - I know, I just helped
another person with exactly that type of problem (trying to use
Application.GoTo on a protected sheet: Error 400).


vcff said:
Should be as I highlighted the code and paste it
 
G

Guest

sorry, I miss out one point, under option 1, after it shown the error 400,
upon clicking ok the worksheet was unprotected.

vcff said:
sorry for the trouble

I had tried again by copy n paste the code in two ways n the following occured

1. right-click worksheet tab n paste the code, run macro and error 400
with only option of OK or Help (worksheet with passwordword "justme")

2. right-click top right hand corner, view code, add module, paste macro
code.
when run, error n when debug,
selection.Sort to DataOption1 in yellow with a arrow pointing at
DataOption1

tnks



JLatham said:
As Gord said, go through it line by line and make sure your copy is exactly
the same. I actually tested this before posting it. Copy it all from the
Sub all the way down to the End Sub to make sure all changes were
incorporated.

If it errors again, hit the [Debug] option and see which line it halted on.
It will be highlighted in the VBA Editing window.

Most likely cause of Error 400 here would be that the sheet is not being
unprotected. Check that the password to unlock (Unprotect) your sheet is
justme spelled exactly like that. If it is not unprotected, the
coltosort.Select line will fail with that error - I know, I just helped
another person with exactly that type of problem (trying to use
Application.GoTo on a protected sheet: Error 400).


vcff said:
Should be as I highlighted the code and paste it

:

Works fine for me.

Did you copy it exactly as Rick posted?


Gord Dibben MS Excel MVP


range A4:A33 was also highlighted

:

Hi Latham

after copying your code, it gave me a error "400"

:

Ok, I think I see the 'why' of your question - the code you have sorts the
entire UsedRange and not the area A6:A60 which is all you want to sort. I
presume that in your real code you're using the correct password where
needed, so...
Sub sortit()
Dim coltosort As Range
Set coltosort = ActiveSheet.Range("A6:A60")
ActiveSheet.Unprotect Password:="justme"
coltosort.Select
Selection.Sort Key1:=coltosort.Cells(2), _
Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
With ActiveSheet
.Protect Password:="justme"
.EnableSelection = xlNoRestrictions
End With
End Sub

Note the change from .. to . for the interior of the With...End With
statements.

:

Hi all

I created a button in a protected worksheet to allow sorting of data in
column A6 to A60 which does not work. Pls help.

I used the following code which I found in the discussion group. What I need
is to sort only a range of datas in one column, eg A6 to A60

Sub sortit()
Dim coltosort As Range
Set coltosort = Application.InputBox(Prompt:= _
"Select A Column", Type:=8)
ActiveSheet.Unprotect Password:="justme"
ActiveSheet.UsedRange.Sort Key1:=coltosort.Cells(2), _
Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
With ActiveSheet
..Protect Password:="justme"
..EnableSelection = xlNoRestrictions
End With
End Sub
 
G

Guest

Ok, that explains it - you're probably running Excel 2000? The "Data
Option1" (there's also a 2 and 3) didn't exist in 2000. The macro was
probably created on machine with Excel 2003 on it. Easy to fix, just delete
everything in that line from the comma just ahead of DataOption1 to the end
of the line. Code should end up looking like:

Sub sortit()
Dim coltosort As Range
Set coltosort = ActiveSheet.Range("A6:A60")
ActiveSheet.Unprotect Password:="justme"
coltosort.Select
Selection.Sort Key1:=coltosort.Cells(2), _
Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
With ActiveSheet
.Protect Password:="justme"
.EnableSelection = xlNoRestrictions
End With
End Sub


vcff said:
sorry for the trouble

I had tried again by copy n paste the code in two ways n the following occured

1. right-click worksheet tab n paste the code, run macro and error 400
with only option of OK or Help (worksheet with passwordword "justme")

2. right-click top right hand corner, view code, add module, paste macro
code.
when run, error n when debug,
selection.Sort to DataOption1 in yellow with a arrow pointing at
DataOption1

tnks



JLatham said:
As Gord said, go through it line by line and make sure your copy is exactly
the same. I actually tested this before posting it. Copy it all from the
Sub all the way down to the End Sub to make sure all changes were
incorporated.

If it errors again, hit the [Debug] option and see which line it halted on.
It will be highlighted in the VBA Editing window.

Most likely cause of Error 400 here would be that the sheet is not being
unprotected. Check that the password to unlock (Unprotect) your sheet is
justme spelled exactly like that. If it is not unprotected, the
coltosort.Select line will fail with that error - I know, I just helped
another person with exactly that type of problem (trying to use
Application.GoTo on a protected sheet: Error 400).


vcff said:
Should be as I highlighted the code and paste it

:

Works fine for me.

Did you copy it exactly as Rick posted?


Gord Dibben MS Excel MVP


range A4:A33 was also highlighted

:

Hi Latham

after copying your code, it gave me a error "400"

:

Ok, I think I see the 'why' of your question - the code you have sorts the
entire UsedRange and not the area A6:A60 which is all you want to sort. I
presume that in your real code you're using the correct password where
needed, so...
Sub sortit()
Dim coltosort As Range
Set coltosort = ActiveSheet.Range("A6:A60")
ActiveSheet.Unprotect Password:="justme"
coltosort.Select
Selection.Sort Key1:=coltosort.Cells(2), _
Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
With ActiveSheet
.Protect Password:="justme"
.EnableSelection = xlNoRestrictions
End With
End Sub

Note the change from .. to . for the interior of the With...End With
statements.

:

Hi all

I created a button in a protected worksheet to allow sorting of data in
column A6 to A60 which does not work. Pls help.

I used the following code which I found in the discussion group. What I need
is to sort only a range of datas in one column, eg A6 to A60

Sub sortit()
Dim coltosort As Range
Set coltosort = Application.InputBox(Prompt:= _
"Select A Column", Type:=8)
ActiveSheet.Unprotect Password:="justme"
ActiveSheet.UsedRange.Sort Key1:=coltosort.Cells(2), _
Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
With ActiveSheet
..Protect Password:="justme"
..EnableSelection = xlNoRestrictions
End With
End Sub
 
G

Guest

Bingo, that solve my problem.

Thank You JLatham, appreciate you help.

r
have a nice day

JLatham said:
Ok, that explains it - you're probably running Excel 2000? The "Data
Option1" (there's also a 2 and 3) didn't exist in 2000. The macro was
probably created on machine with Excel 2003 on it. Easy to fix, just delete
everything in that line from the comma just ahead of DataOption1 to the end
of the line. Code should end up looking like:

Sub sortit()
Dim coltosort As Range
Set coltosort = ActiveSheet.Range("A6:A60")
ActiveSheet.Unprotect Password:="justme"
coltosort.Select
Selection.Sort Key1:=coltosort.Cells(2), _
Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
With ActiveSheet
.Protect Password:="justme"
.EnableSelection = xlNoRestrictions
End With
End Sub


vcff said:
sorry for the trouble

I had tried again by copy n paste the code in two ways n the following occured

1. right-click worksheet tab n paste the code, run macro and error 400
with only option of OK or Help (worksheet with passwordword "justme")

2. right-click top right hand corner, view code, add module, paste macro
code.
when run, error n when debug,
selection.Sort to DataOption1 in yellow with a arrow pointing at
DataOption1

tnks



JLatham said:
As Gord said, go through it line by line and make sure your copy is exactly
the same. I actually tested this before posting it. Copy it all from the
Sub all the way down to the End Sub to make sure all changes were
incorporated.

If it errors again, hit the [Debug] option and see which line it halted on.
It will be highlighted in the VBA Editing window.

Most likely cause of Error 400 here would be that the sheet is not being
unprotected. Check that the password to unlock (Unprotect) your sheet is
justme spelled exactly like that. If it is not unprotected, the
coltosort.Select line will fail with that error - I know, I just helped
another person with exactly that type of problem (trying to use
Application.GoTo on a protected sheet: Error 400).


:

Should be as I highlighted the code and paste it

:

Works fine for me.

Did you copy it exactly as Rick posted?


Gord Dibben MS Excel MVP


range A4:A33 was also highlighted

:

Hi Latham

after copying your code, it gave me a error "400"

:

Ok, I think I see the 'why' of your question - the code you have sorts the
entire UsedRange and not the area A6:A60 which is all you want to sort. I
presume that in your real code you're using the correct password where
needed, so...
Sub sortit()
Dim coltosort As Range
Set coltosort = ActiveSheet.Range("A6:A60")
ActiveSheet.Unprotect Password:="justme"
coltosort.Select
Selection.Sort Key1:=coltosort.Cells(2), _
Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
With ActiveSheet
.Protect Password:="justme"
.EnableSelection = xlNoRestrictions
End With
End Sub

Note the change from .. to . for the interior of the With...End With
statements.

:

Hi all

I created a button in a protected worksheet to allow sorting of data in
column A6 to A60 which does not work. Pls help.

I used the following code which I found in the discussion group. What I need
is to sort only a range of datas in one column, eg A6 to A60

Sub sortit()
Dim coltosort As Range
Set coltosort = Application.InputBox(Prompt:= _
"Select A Column", Type:=8)
ActiveSheet.Unprotect Password:="justme"
ActiveSheet.UsedRange.Sort Key1:=coltosort.Cells(2), _
Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
With ActiveSheet
..Protect Password:="justme"
..EnableSelection = xlNoRestrictions
End With
End Sub
 
G

Gord Dibben

Good catch Jerry

Gord

Ok, that explains it - you're probably running Excel 2000? The "Data
Option1" (there's also a 2 and 3) didn't exist in 2000. The macro was
probably created on machine with Excel 2003 on it. Easy to fix, just delete
everything in that line from the comma just ahead of DataOption1 to the end
of the line. Code should end up looking like:

Sub sortit()
Dim coltosort As Range
Set coltosort = ActiveSheet.Range("A6:A60")
ActiveSheet.Unprotect Password:="justme"
coltosort.Select
Selection.Sort Key1:=coltosort.Cells(2), _
Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
With ActiveSheet
.Protect Password:="justme"
.EnableSelection = xlNoRestrictions
End With
End Sub


vcff said:
sorry for the trouble

I had tried again by copy n paste the code in two ways n the following occured

1. right-click worksheet tab n paste the code, run macro and error 400
with only option of OK or Help (worksheet with passwordword "justme")

2. right-click top right hand corner, view code, add module, paste macro
code.
when run, error n when debug,
selection.Sort to DataOption1 in yellow with a arrow pointing at
DataOption1

tnks



JLatham said:
As Gord said, go through it line by line and make sure your copy is exactly
the same. I actually tested this before posting it. Copy it all from the
Sub all the way down to the End Sub to make sure all changes were
incorporated.

If it errors again, hit the [Debug] option and see which line it halted on.
It will be highlighted in the VBA Editing window.

Most likely cause of Error 400 here would be that the sheet is not being
unprotected. Check that the password to unlock (Unprotect) your sheet is
justme spelled exactly like that. If it is not unprotected, the
coltosort.Select line will fail with that error - I know, I just helped
another person with exactly that type of problem (trying to use
Application.GoTo on a protected sheet: Error 400).


:

Should be as I highlighted the code and paste it

:

Works fine for me.

Did you copy it exactly as Rick posted?


Gord Dibben MS Excel MVP


range A4:A33 was also highlighted

:

Hi Latham

after copying your code, it gave me a error "400"

:

Ok, I think I see the 'why' of your question - the code you have sorts the
entire UsedRange and not the area A6:A60 which is all you want to sort. I
presume that in your real code you're using the correct password where
needed, so...
Sub sortit()
Dim coltosort As Range
Set coltosort = ActiveSheet.Range("A6:A60")
ActiveSheet.Unprotect Password:="justme"
coltosort.Select
Selection.Sort Key1:=coltosort.Cells(2), _
Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
With ActiveSheet
.Protect Password:="justme"
.EnableSelection = xlNoRestrictions
End With
End Sub

Note the change from .. to . for the interior of the With...End With
statements.

:

Hi all

I created a button in a protected worksheet to allow sorting of data in
column A6 to A60 which does not work. Pls help.

I used the following code which I found in the discussion group. What I need
is to sort only a range of datas in one column, eg A6 to A60

Sub sortit()
Dim coltosort As Range
Set coltosort = Application.InputBox(Prompt:= _
"Select A Column", Type:=8)
ActiveSheet.Unprotect Password:="justme"
ActiveSheet.UsedRange.Sort Key1:=coltosort.Cells(2), _
Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
With ActiveSheet
..Protect Password:="justme"
..EnableSelection = xlNoRestrictions
End With
End Sub
 
G

Guest

You're welcome.

The sort will work just as well without that part of the command - and will
even run as expected in 2003 if you ever move on to it.

vcff said:
Bingo, that solve my problem.

Thank You JLatham, appreciate you help.

r
have a nice day

JLatham said:
Ok, that explains it - you're probably running Excel 2000? The "Data
Option1" (there's also a 2 and 3) didn't exist in 2000. The macro was
probably created on machine with Excel 2003 on it. Easy to fix, just delete
everything in that line from the comma just ahead of DataOption1 to the end
of the line. Code should end up looking like:

Sub sortit()
Dim coltosort As Range
Set coltosort = ActiveSheet.Range("A6:A60")
ActiveSheet.Unprotect Password:="justme"
coltosort.Select
Selection.Sort Key1:=coltosort.Cells(2), _
Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
With ActiveSheet
.Protect Password:="justme"
.EnableSelection = xlNoRestrictions
End With
End Sub


vcff said:
sorry for the trouble

I had tried again by copy n paste the code in two ways n the following occured

1. right-click worksheet tab n paste the code, run macro and error 400
with only option of OK or Help (worksheet with passwordword "justme")

2. right-click top right hand corner, view code, add module, paste macro
code.
when run, error n when debug,
selection.Sort to DataOption1 in yellow with a arrow pointing at
DataOption1

tnks



:

As Gord said, go through it line by line and make sure your copy is exactly
the same. I actually tested this before posting it. Copy it all from the
Sub all the way down to the End Sub to make sure all changes were
incorporated.

If it errors again, hit the [Debug] option and see which line it halted on.
It will be highlighted in the VBA Editing window.

Most likely cause of Error 400 here would be that the sheet is not being
unprotected. Check that the password to unlock (Unprotect) your sheet is
justme spelled exactly like that. If it is not unprotected, the
coltosort.Select line will fail with that error - I know, I just helped
another person with exactly that type of problem (trying to use
Application.GoTo on a protected sheet: Error 400).


:

Should be as I highlighted the code and paste it

:

Works fine for me.

Did you copy it exactly as Rick posted?


Gord Dibben MS Excel MVP


range A4:A33 was also highlighted

:

Hi Latham

after copying your code, it gave me a error "400"

:

Ok, I think I see the 'why' of your question - the code you have sorts the
entire UsedRange and not the area A6:A60 which is all you want to sort. I
presume that in your real code you're using the correct password where
needed, so...
Sub sortit()
Dim coltosort As Range
Set coltosort = ActiveSheet.Range("A6:A60")
ActiveSheet.Unprotect Password:="justme"
coltosort.Select
Selection.Sort Key1:=coltosort.Cells(2), _
Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
With ActiveSheet
.Protect Password:="justme"
.EnableSelection = xlNoRestrictions
End With
End Sub

Note the change from .. to . for the interior of the With...End With
statements.

:

Hi all

I created a button in a protected worksheet to allow sorting of data in
column A6 to A60 which does not work. Pls help.

I used the following code which I found in the discussion group. What I need
is to sort only a range of datas in one column, eg A6 to A60

Sub sortit()
Dim coltosort As Range
Set coltosort = Application.InputBox(Prompt:= _
"Select A Column", Type:=8)
ActiveSheet.Unprotect Password:="justme"
ActiveSheet.UsedRange.Sort Key1:=coltosort.Cells(2), _
Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
With ActiveSheet
..Protect Password:="justme"
..EnableSelection = xlNoRestrictions
End With
End Sub
 
G

Guest

ok will keep that in mind.

tnks for all the efforts thelping me to solve the problem.

r

JLatham said:
You're welcome.

The sort will work just as well without that part of the command - and will
even run as expected in 2003 if you ever move on to it.

vcff said:
Bingo, that solve my problem.

Thank You JLatham, appreciate you help.

r
have a nice day

JLatham said:
Ok, that explains it - you're probably running Excel 2000? The "Data
Option1" (there's also a 2 and 3) didn't exist in 2000. The macro was
probably created on machine with Excel 2003 on it. Easy to fix, just delete
everything in that line from the comma just ahead of DataOption1 to the end
of the line. Code should end up looking like:

Sub sortit()
Dim coltosort As Range
Set coltosort = ActiveSheet.Range("A6:A60")
ActiveSheet.Unprotect Password:="justme"
coltosort.Select
Selection.Sort Key1:=coltosort.Cells(2), _
Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
With ActiveSheet
.Protect Password:="justme"
.EnableSelection = xlNoRestrictions
End With
End Sub


:

sorry for the trouble

I had tried again by copy n paste the code in two ways n the following occured

1. right-click worksheet tab n paste the code, run macro and error 400
with only option of OK or Help (worksheet with passwordword "justme")

2. right-click top right hand corner, view code, add module, paste macro
code.
when run, error n when debug,
selection.Sort to DataOption1 in yellow with a arrow pointing at
DataOption1

tnks



:

As Gord said, go through it line by line and make sure your copy is exactly
the same. I actually tested this before posting it. Copy it all from the
Sub all the way down to the End Sub to make sure all changes were
incorporated.

If it errors again, hit the [Debug] option and see which line it halted on.
It will be highlighted in the VBA Editing window.

Most likely cause of Error 400 here would be that the sheet is not being
unprotected. Check that the password to unlock (Unprotect) your sheet is
justme spelled exactly like that. If it is not unprotected, the
coltosort.Select line will fail with that error - I know, I just helped
another person with exactly that type of problem (trying to use
Application.GoTo on a protected sheet: Error 400).


:

Should be as I highlighted the code and paste it

:

Works fine for me.

Did you copy it exactly as Rick posted?


Gord Dibben MS Excel MVP


range A4:A33 was also highlighted

:

Hi Latham

after copying your code, it gave me a error "400"

:

Ok, I think I see the 'why' of your question - the code you have sorts the
entire UsedRange and not the area A6:A60 which is all you want to sort. I
presume that in your real code you're using the correct password where
needed, so...
Sub sortit()
Dim coltosort As Range
Set coltosort = ActiveSheet.Range("A6:A60")
ActiveSheet.Unprotect Password:="justme"
coltosort.Select
Selection.Sort Key1:=coltosort.Cells(2), _
Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
With ActiveSheet
.Protect Password:="justme"
.EnableSelection = xlNoRestrictions
End With
End Sub

Note the change from .. to . for the interior of the With...End With
statements.

:

Hi all

I created a button in a protected worksheet to allow sorting of data in
column A6 to A60 which does not work. Pls help.

I used the following code which I found in the discussion group. What I need
is to sort only a range of datas in one column, eg A6 to A60

Sub sortit()
Dim coltosort As Range
Set coltosort = Application.InputBox(Prompt:= _
"Select A Column", Type:=8)
ActiveSheet.Unprotect Password:="justme"
ActiveSheet.UsedRange.Sort Key1:=coltosort.Cells(2), _
Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
With ActiveSheet
..Protect Password:="justme"
..EnableSelection = xlNoRestrictions
End With
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top