Results 1 to 9 of 9

'Run-time error 1004'

This is a discussion on 'Run-time error 1004' within the Excel Questions forums, part of the Question Forums category; I have this code in my worksheet: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim cmtText As String ...

  1. #1
    New Member
    Join Date
    Feb 2008
    Posts
    4

    Default 'Run-time error 1004'

    I have this code in my worksheet:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim cmtText As String
    Cancel = True
    Me.Unprotect "Thermo544"
    cmtText = InputBox("Enter Comment")
    If cmtText = "" Then
    Me.Protect "Thermo544"
    Exit Sub
    End If
    Target.ClearComments
    Target.AddComment
    Target.Comment.Text Text:=cmtText
    Me.Protect "Thermo544"
    End Sub

    I get the run-time error 1004 message: "Method 'Unprotect' of object '_Worksheet' failed" when I try to run the macro in a shared workbook. Any suggestions on how this macro can be enabled to run in a shared workbook?

    Thanks

  2. #2
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    32,332

    Default Re: 'Run-time error 1004'

    You cannot Unprotect Sheets via VBA code on a shared workbook. Sorry, no way around that.

    But instead of standard protection, you could modify your code to protect (while it's NOT shared) to use UserInterfaceOnly...

    So UNShare the book..
    Then remove any code in your book that Protects/unprotects sheets...

    run a macro to protect all sheets you want protected using UserInterfaceOnly = TRUE

    Sheets("Sheet1").Protect Password:="PasswordHere", UserInterfaceOnly:=True

    Then reshare the workbook.
    Now your VBA code can modify sheets, but users can't
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  3. #3
    Board Regular
    Join Date
    Feb 2008
    Posts
    63

    Default Re: 'Run-time error 1004'

    I had posted an idea, then saw JonMo1's post,
    You cannot Unprotect Sheets via VBA code on a shared workbook.
    Go figure. I Love how we just aren't allowed to do certain things. Good to know Jon, thanks. I know thats going to come up at ym office eventually.

    Jason

  4. #4
    New Member
    Join Date
    Feb 2008
    Posts
    4

    Default Re: 'Run-time error 1004'

    That's a no go.

    My code now looks like this:

    Option Explicit
    Public oldRange As Range

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim cmtText As String
    Cancel = True
    Sheets("Sheet1").UnProtect Password:="Thermo544", UserInterfaceOnly:=True
    cmtText = InputBox("Enter Comment")
    If cmtText = "" Then
    Sheets("Sheet1").Protect Password:="Thermo544", UserInterfaceOnly:=True
    Exit Sub
    End If
    Target.ClearComments
    Target.AddComment
    Target.Comment.Text Text:=cmtText
    Sheets("Sheet1").Protect Password:="Thermo544", UserInterfaceOnly:=True
    End Sub

    The run-time error 1004 now states: "Application-defined or object-defined error."

    Additionally, when I replace the word highlighted in red with "Protect" the run-time 1004 error says: "The cell or chart you are trying to change is protected and therefore read-only. To modify a protected cell or chart, first remove protection using the Unprotect Sheet command (Tools menu, Protection submenu). You may be prompted for a password."

    I am not sure if this is exactly what you were suggesting I do. I will try to ponder some other ideas to see if I can make it work.

    Thanks

  5. #5
    Board Regular
    Join Date
    Feb 2008
    Posts
    63

    Default Re: 'Run-time error 1004'

    I just wrote this very simple test in a new workbook I made up:
    Code:
     
    Option Explicit
     
    Sub lockit()
      Sheets("sheet1").Protect Password:="abc123"
    End Sub
     
    Sub unlockit()
      Sheets("sheet1").Unprotect Password:="abc123"
    End Sub
    It works just fine. Remeber what Jon wrote just before me...
    You cannot Unprotect Sheets via VBA code on a shared workbook.
    That might be your entire problem.
    Jason

    update:
    I forgot to add the userinterface part. That is only required on the protecting, not the unprotecting I believe. I got an error when trying to unprotect with that argument on, but not without it.

  6. #6
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    32,332

    Default Re: 'Run-time error 1004'



    That's a no go.

    My code now looks like this:

    Option Explicit
    Public oldRange As Range

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim cmtText As String
    Cancel = True
    Sheets("Sheet1").UnProtect Password:="Thermo544", UserInterfaceOnly:=True
    cmtText = InputBox("Enter Comment")
    If cmtText = "" Then
    Sheets("Sheet1").Protect Password:="Thermo544", UserInterfaceOnly:=True
    Exit Sub
    End If
    Target.ClearComments
    Target.AddComment
    Target.Comment.Text Text:=cmtText
    Sheets("Sheet1").Protect Password:="Thermo544", UserInterfaceOnly:=True
    End Sub

    The run-time error 1004 now states: "Application-defined or object-defined error."
    REMOVE ALL CODE THAT PROTECTS or UNPROTECTS SHEETS. - and DON'T put it back.

    UnShare your book.

    Ran a macro that protects sheets using UserInterfaceOnly = TRUE.
    You will only need to Run that macro once.

    Reshare your book.

    You will no longer need to unprotect/reprotect your sheets in VBA.
    Your sheets will be protected from users modifying them, but VBA code WILL be able to modify sheets WITHOUT needing to UNprotect them.
    Last edited by Jonmo1; Feb 22nd, 2008 at 10:35 AM.
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  7. #7
    Board Regular
    Join Date
    Feb 2008
    Posts
    63

    Default Re: 'Run-time error 1004'

    I found this on the web:
    ActiveSheet.EnableSelection = xlUnlockedCells

    this has to be performed each time the workbook is opened. Unlike the
    manual setting, this setting is not persistent when performed via code (it
    isn't an option in the Protect command). An apparent oversight by
    [COLOR=blue! important][COLOR=blue! important]Microsoft[/COLOR][/COLOR].
    at
    http://www.excelforum.com/showthread.php?t=382012

    Meaning that this might not work after saving and reopening the workbooks again. Test it and let us know.

    jason

  8. #8
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    32,332

    Default Re: 'Run-time error 1004'

    You are right Jax, apparently there's nothing you can do.

    Once you save / close / reopen the workbook, the UserInterFace = TRUE is lost...
    And you CAN'T run a macro to ReSet it when the book is shared...You would have to UNshare it, run the code to set the userinterfaceonly, then reshare the book.

    And No, you can't use code to UNshare the book either. I tried....

    This is one reason most people don't like using shared workbooks...
    There are lots of things you can't do to a shared workbook, it sucks...

    You might consider using ACCESS for your users to enter data into then.
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  9. #9
    New Member
    Join Date
    Feb 2008
    Posts
    4

    Default Re: 'Run-time error 1004'

    Yes..I thought I was going to pull my hair out trying to get your code to work. It does suck and Access sounds like a much better option.

    Thanks to you both for your help

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com