none
macros error in a shared workbook RRS feed

  • Question

  • 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?

    Monday, November 26, 2012 3:15 PM

All replies

  • Try changing  Protect UserInterfaceOnly:=True to

    ActiveSheet.Protect UserInterfaceOnly:=True

    Monday, November 26, 2012 5:47 PM
  • got error message 1004.

    Tuesday, November 27, 2012 7:53 AM
  • The error is not from that line (or any of the code you posted). What line is highlighted when you run the code and get that error?
    Tuesday, November 27, 2012 2:47 PM
  • the lines highlighed are:

    Selection.Sort Key1:=Range("D4"), Order1:=xlAscending, Header:=xlGuess, _
            OrderCustom
    :=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1
    :=xlSortNormal

    Wednesday, November 28, 2012 7:49 AM
  • Then you may have merged cells...  or other things that interfere with sorting, but merged cells is the usual culprit.
    Wednesday, November 28, 2012 1:43 PM