ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   macro for sorting in protected sheet (https://www.excelbanter.com/excel-discussion-misc-queries/162882-macro-sorting-protected-sheet.html)

vcff
macro for sorting in protected sheet
 
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

Billy Liddel
macro for sorting in protected sheet
 
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

"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

JLatham
macro for sorting in protected sheet
 
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

vcff
macro for sorting in protected sheet
 
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

vcff
macro for sorting in protected sheet
 
excel 2000 so no luck for that.

"Billy Liddel" wrote:

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

"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

vcff
macro for sorting in protected sheet
 
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

Gord Dibben
macro for sorting in protected sheet
 
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

vcff
macro for sorting in protected sheet
 
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

Gord Dibben
macro for sorting in protected sheet
 
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

On Sat, 20 Oct 2007 10:53:00 -0700, 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

JLatham
macro for sorting in protected sheet
 
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

vcff
macro for sorting in protected sheet
 
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

vcff
macro for sorting in protected sheet
 
sorry, I miss out one point, under option 1, after it shown the error 400,
upon clicking ok the worksheet was unprotected.

"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

JLatham
macro for sorting in protected sheet
 
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

vcff
macro for sorting in protected sheet
 
Bingo, that solve my problem.

Thank You JLatham, appreciate you help.

r
have a nice day

"JLatham" wrote:

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

Gord Dibben
macro for sorting in protected sheet
 
Good catch Jerry

Gord

On Sun, 21 Oct 2007 03:52:00 -0700, JLatham <HelpFrom @
Jlathamsite.com.(removethis) wrote:

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

JLatham
macro for sorting in protected sheet
 
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" wrote:

Bingo, that solve my problem.

Thank You JLatham, appreciate you help.

r
have a nice day

"JLatham" wrote:

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

vcff
macro for sorting in protected sheet
 
ok will keep that in mind.

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

r

"JLatham" wrote:

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

Bingo, that solve my problem.

Thank You JLatham, appreciate you help.

r
have a nice day

"JLatham" wrote:

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


All times are GMT +1. The time now is 07:00 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com