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

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

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

Should be as I highlighted the code and paste it

"Gord Dibben" wrote:

Works fine for me.

Did you copy it exactly as Rick posted?


Gord Dibben MS Excel MVP

On Sat, 20 Oct 2007 10:12:00 -0700, vcff wrote:

range A4:A33 was also highlighted

"vcff" wrote:

Hi Latham

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

"JLatham" wrote:

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.

"vcff" wrote:

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