Unprotect sheet in Shared workbook ?

[Cameronl]

Hi I have a Function that uses the following lines of code (a) on entry to the Function and (b) on exit of the function. The code stops working when I share the workbook it throws this error

"Unprotect method of WorkSheet class failed"

(a)ActiveWorkbook.Worksheets("aSheet.xls").Unprotect Password:=aPassword
(b) ActiveWorkbook.Worksheets("aSheet.xls").Protect Password:=aPassword

Do I have to change the code when working with a shared workbook ? If so how ?

Thanks

[ChemistB]

You need to unprotect the sharing first (do you have that password protected also?).

    ActiveWorkbook.UnprotectSharing (PWORD)

should work where PWORD = your password or leave it out if you're sharing without password. NOTE: there is a bug where you can't use VBA to unprotect the workbook when you're using protected shared. I learned this the hard way. There is no problem with protected worksheets however. Does this help?

[Cameronl]

Thanks for response ... it did but unfortunately creates mores problems. I want the tracking history to be kept (feature available from shared workbook), when I unProtect the sharing it wipes this out.

[Cameronl]

I actually don't want to use a shared workbook, I just want the track changes functionality that the shared workbook provides.
Don't suppose you know how i could get track changes without sharing the workbook ?

[ChemistB]

Unfortunately the Track changes comes with the Sharing and removing the sharing removes the tracking.

You could write a macro that
1. Tracks the changes on a separate worksheet (Excel will name that worksheet "History")
2. Copy History to a new sheet in another workbook Which you use to keep a running track of your history.
3. Then continue with your previous history safely preserved.

Sorry, there's no easy way.

[Cameronl]

Found a solution to this here's the link http://www.ozgrid.com/Excel/track-changes.htm

Thanks

[ChemistB]

Hi Cameroni,
Nice link but I still don't see where it says anything that will help you. What is your solution? I'm curious because I use this feature fairly regularly. Thanks

Nevermind, just found the link to the Tracking VBA method. Thanks