Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to enable a macro in protected sheet | Excel Discussion (Misc queries) | |||
Sorting a protected sheet | Excel Worksheet Functions | |||
Macro Error when Sheet is Protected | Excel Discussion (Misc queries) | |||
use macro button to run macro in protected sheet | Excel Discussion (Misc queries) | |||
How can you create a macro on a protected sheet? | Excel Discussion (Misc queries) |