# Share and protect a workbook with a macro in excel 2003



## XLtech (Sep 15, 2010)

Hi best Excel users,

I have a deep problem with excel 2003 running on windows XP. My problem is that I’m trying to mimic the action of sharing and protecting a workbook (that you get from going to tools > Protection > Protect and share workbook) by writing a macro. Also I want to be able to unprotect and protect and re-share the workbook after making changes. All that should be done in macros.

Let’s suggest that you have a workbook (with 3 sheets: "Sh1"; "Sh2" and "database"). At start when you open the workbook you need to have the database hidden and the workbook should be shared and fully protected. Adding something to the database starts by pushing a macro button that will unprotect the workbook and unhide the database tab. When you finish you need to restore protection and hide the database tab with another macro button. And just in case something happens you need to have a BeforeClose event that make sure that your workbook is shared, fully protected and your database tab is hidden each time you close the excel window. 

Is that possible? I’ve been working on that for 1 week now with no satisfying results. I hope sincerely that there is someone out there with the required knowledge to solve this problem.

Many thanks to you all 
http://www.techsupportforum.com/images/smilies/wave.gif


----------



## RSpecianjr (Jan 20, 2010)

Hey Xltech,

Yeah, that should be fairly easy to do. It's been a while since i've looked at protect/unprotect but if I remember correctly it was a single line of code. How do you mean sharing though? Do you just want to save the file to a network location or did you have something else in mind? 

Thanks,

Robert D. Specian Jr.


----------



## XLtech (Sep 15, 2010)

Thanks a lot for your answer,

At start the workbook is shared and protected and the structure is also fully protected by a password ”mypass” in that case. The Database sheet is hidden.

The first sub (unprotect) is used to unprotect and unshare the workbook and to make the sheet (Database) visible. This sub is working perfectly. 

Sub unprotect()
a = InputBox("Please enter password")
If a <> "mypass" Then
MsgBox "Wrong password!"
Worksheets("Database").Visible = False
End If
If a = "mypass" Then
Application.DisplayAlerts = False
ActiveWorkbook.UnprotectSharing SharingPassword:="mypass"
Application.DisplayAlerts = True
ActiveWorkbook.Unprotect "mypass"
Worksheets("Database").Visible = True
End If
End Sub


The second sub (shareAndprotect) is used to hide the Database and re-share and re-protect the workbook as it was at start. The problem is that this sub is not working properly. I think because the line (ActiveWorkbook.Protect "mypass", Structure:=True) to protect the structure of the workbook and the line (ActiveWorkbook.ProtectSharing Password:="mypass", SharingPassword:="mypass") to share and protect sharing does not co-exist. Anyway I believe that it should be a way to restore the workbook sharing and protection properties as there were at start but I don’t have the knowledge to solve this problem. 



Sub shareAndprotect()
Sheets("Database").Visible = False
Application.DisplayAlerts = False
ActiveWorkbook.Protect "mypass", Structure:=True
ActiveWorkbook.SaveAs ActiveWorkbook.FullName
ActiveWorkbook.ProtectSharing Password:="mypass", SharingPassword:="mypass"
Application.DisplayAlerts = True
End Sub 

Thanks a lot again for your answer and I hope you can help me further on that issue.


----------



## RSpecianjr (Jan 20, 2010)

Hey,

Do you have a workbook you can post, that we can play around with?

Thanks,

Robert


----------



## XLtech (Sep 15, 2010)

Hi again Robert,

I'm sending the test workbook as an attachment. Have also a look at the OnOpen and BeforeClose events that I disabled. The sub routine to save changes, re-share the workbook, protect sharing and protect the structure is not working as it should. 

The password has been sent to you as a private message.

Many thanks


----------

