'Run-time error 1004'

[cmm0812]

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

 

[Jonmo1]

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

 

[JaxJason]

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

 

[cmm0812]

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

 

[JaxJason]

I just wrote this very simple test in a new workbook I made up:

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.

 

[Jonmo1]

<!-- / icon and title --><!-- message -->

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.

 

[JaxJason]

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][FONT=Verdana,Helvetica,sans-serif][COLOR=blue! important][FONT=Verdana,Helvetica,sans-serif]Microsoft[/FONT][/FONT][/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

 

[Jonmo1]

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.

 

[cmm0812]

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