0

I have a shared and protected worsheet in excel 2012. (protected but without password) I wrote this macro: 

Private Sub CommandButton1_Click()
    ActiveSheet.Unprotect
    Range("B4:K38").Select
    Selection.Sort Key1:=Range("D4"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
   'cells I don't want to be protect (to be user-editable)
    Range("B4:K38").Locked = False
   'protecting the sheet, and all the other cells will be protected
    Protect UserInterfaceOnly:=True
End Sub

But everytime I use this macro I have a message that shows me that there's an error with the method protect.

What am I doing wrong?

Community
  • 1
  • 1
Pramine
  • 31
  • 7
  • What does the error message say? – Olle Sjögren Nov 26 '12 at 15:22
  • 1
    You don't need to unprotect to run vba on a sheet if it is protected using userinterfaceonly, that setting is designed so you do not need to unprotect. – scott Nov 26 '12 at 15:25
  • well, what I need is to sort by colomn D4 and the users wont have permission to write anything outside the table that is from B4 to K38.please help me !!! – Pramine Nov 26 '12 at 15:40

1 Answers1

0

Try this, not tested since on mobile

rather than using protect userinterfaceonly:=true

    with activesheet
         .EnableSelection = xlUnlockedCells 
         .Protect UserInterfaceOnly:=True 
    End With

Also using UserInterfaceOnly you shouldn't need to unprotect the sheet to edit it with vba.

scott
  • 2,235
  • 1
  • 14
  • 18
  • doesn't work. 1004 run time error. how would you do it? think that you have a shared workbook and you want users that share it only can edit some cells. I don't know why is too hard to do it !!!! may be I have vba knowledge lack. – Pramine Nov 27 '12 at 08:01
  • Try the edit if not i'll get to a machine with full excel and try some examples – scott Nov 27 '12 at 14:22