macro fails when i have sheet protection on!!!

[j4ymf]

hello all

i have been designing a sheet, i have just about finished it so i thought i would protect the sheet so no one can mess up my formulas.

i have just 1 bit that fails when i have protection turned on, i need to run this macro.

Sub Macro4()
'
' Macro4 Macro
' Macro recorded 05/10/2006 by me
'

'
    ActiveWindow.SmallScroll Down:=6
    Range("B202:F249").Select
    ActiveWindow.SmallScroll Down:=-27
    Selection.Sort Key1:=Range("E202"), Order1:=xlAscending, Header:=xlGuess _
        , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal    ActiveWindow.SmallScroll Down:=-15
    Range("B201").Select
End Sub

the bold bit is the bit that is highlighted

it works fine with out protection, i have unlocked the cells any ideas

thanks jason

 

[Smitty]

You can Unprotect/Protect in your code. And noo need for all that selecting or screen navigation either:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> Macro4()
    ActiveSheet.Unprotect "PasswordHere"
        Range("B202:F249").Sort Key1:=Range("E202"), Order1:=xlAscending, Header:=xlGuess _
            , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
    ActiveSheet.Protect "PasswordHere"
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

Smitty

 

[j4ymf]

thankyou

you are the best

it worked fantastic

 

[j4ymf]

hello all

im pulling my hair out i have been playing with this code all week at home on office 2003, sent it into work using office 97 and i get a debug error it just wont work!!!!

works fine at home but fails at work any ideas,

Sub Macro4()
    ActiveSheet.Unprotect "PasswordHere"
        Range("B202:F249").Sort Key1:=Range("E202"), Order1:=xlAscending, Header:=xlGuess _
            , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
    ActiveSheet.Protect "PasswordHere"
End Sub

the bit it fails on or the bit that is highlighted is dataoption1


please cast some light on it
many thanks jason

 

[Norie]

Jason

Just delete that bit, it's not supported in earlier versions of Excel VBA.

Sub Macro4()
    ActiveSheet.Unprotect "PasswordHere"
        Range("B202:F249").Sort Key1:=Range("E202"), Order1:=xlAscending, Header:=xlGuess _
            , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom            
    ActiveSheet.Protect "PasswordHere"
End Sub

 

[j4ymf]

thankyou i will try tomorrow

u r a star