Free Microsoft Excel 2013 Quick Reference

Protecting and Sharing a Workbook

Hello all,

How can I protect and share a workbook where the users are only able to put informaiton in certain cells (some of my cells contain formulas that I don't want the user to touch). I have tried protect the workbook and only allowing unlocked cells to be select and share it. However, I'm not getting the desired results.

Thanks for your help.


Post your answer or comment

comments powered by Disqus
Hello all. I am trying to share a worbook that contains macros. One sheet in this book, "Stats", is protected upon opening with the code:

	VB:
	
 Workbook_open() 
    Dim sh As Worksheet 
    Set sh = Sheets("Stats") 
    sh.EnableAutoFilter = True 
    sh.Protect contents:=True, userInterfaceOnly:=True, Password:="rats" 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This was to allow the macros to run on this protected sheet. The problem is when i set this workbook to Shared, opening it causes the error:

Run-time error '1004': Method 'Protect' of object '_Worksheet' failed

Any ideas on how i can share this successfully?

Hello all. I am trying to share a worbook that contains macros. One sheet in this book, "Stats", is protected upon opening with the code:

Private Sub Workbook_open()
Dim sh As Worksheet
Set sh = Sheets("Stats")
sh.EnableAutoFilter = True
sh.Protect contents:=True, userInterfaceOnly:=True, Password:="rats"
End Sub

This was to allow the macros to run on this protected sheet. The problem is when i set this workbook to Shared, opening it causes the error:

Run-time error '1004': Method 'Protect' of object '_Worksheet' failed

Any ideas on how i can share this successfully?

HI everyone,

I have a workbook that consists of 3 sheets ("Log in", "Visible" and "Database"). At start I have an "Open event" that secure that the workbook is fully protected and shared. The sheet "Database" is hidden at start and outline is active in sheet "Visible".

Here is the macro:

Private Sub Workbook_Open()

ActiveWorkbook.UnprotectSharing SharingPassword:="mypass"
ActiveWorkbook.Unprotect "mypass"
Worksheets("Database").Visible = False
With ActiveWorkbook
If .MultiUserEditing Then
Application.DisplayAlerts = False
.ExclusiveAccess
.Protect Structure:=False, Windows:=False
.UnprotectSharing SharingPassword:="mypass"
Application.DisplayAlerts = True
End If
End With

With Worksheets("Visible")
.EnableOutlining = True
.Unprotect "mypass"
.Protect Password:="mypass", UserInterfaceOnly:=True
End With

With ActiveWorkbook
Application.DisplayAlerts = False
.Protect "mypass", Structure:=True
.SaveAs ActiveWorkbook.FullName
ThisWorkbook.SaveAs Filename:=ThisWorkbook.FullName, Accessmode:=xlShared
ProtectSharing Password:="mypass", SharingPassword:="mypass"
Application.DisplayAlerts = True
End With

End Sub

The above macro is not working properly because it doesn't protect the sharing of the workbook.

Later on I have another macro to Unprotect the workbook, disable shared and make the worksheet "Database" visible and open for changes.

Here is the macro:
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
This macro is not working properly either. When running it displays numerous ERROR messages depending on the changes I make to the macro. It's very unstable and partially not working. So I hope to find someone with enough knowledge to help me solve the problem.

Many thanks

Hi

I have made a spreadsheet which needs to be used and edited by up to 20 people. The columns in the spreadsheet need to have a auto filter on them. I set this up fine, however when i protect and share the workbook the only way i can remove the filters is to click on each individual column and pick all from the drop down list. As there is a sheet for the each month (for the last 6 months) and 12 columns on each sheet it takes a long time to remove the filters so all records can be seen.

I was wondering if there was a macro that could do this, then i could just have a button at the top of each sheet to remove the filters. I can do this fine until i protect the workbook. At the moment i have the worksheet unprotected which is not the way i want it.

If anyone could help me i would be very happy

My apologies to viewers who have read this post already. I forgot to put a title on my last post.

As part of a string of answers to a previous question (see “Why do I encounter problems running macros in a shared workbook?”), I’m left with the impression that I cannot successfully run macros in a shared workbook if I protect worksheets/workbooks. The reason I’d like to have worksheet protection is that I have many data forms set up and there are some data forms where I do not want users entering or editing existing data fields (sheet protection grays out the cells and the “New” button”). If anyone has successfully protected and unprotected a sheet using a macro in a shared workbook, I’d love to hear about it. I’ve tried setting up some very simple examples and have not been able to get them to work.

I read the Excel help menu on the limitations of workbooks and it does not say that you cannot use protection features on shared workbooks

(From the help menu) In a shared workbook, you cannot do the following:

Passwords
· Assign a password to protect individual worksheets or the entire workbook. Protection that you applied before sharing the workbook remains in effect after you share the workbook.
· Change or remove passwords. Passwords that you assigned before sharing the workbook remain in effect after you share the workbook.
Macros
Write, change, view, record, or assign macros. However, you can record operations in a shared workbook into a macro stored in another workbook that isn't shared. In a shared workbook, you can run macros that were created before you shared the workbook; although if you run a macro that includes an unavailable operation, the macro stops running when it reaches the unavailable operation.

Any comments?

Hi,
I am trying to pw-protect the sheet, the pw-protect workbook
and the pw-protect sharing in code. I can do this manually in Excel
but not in Excel VBA or Access VBA code. I can protect the
sheet/workbook and sheet/sharing combination but not all three. Users
can rename worksheets, etc, with the workbook unprotected.

Below is my code that gives a run-time error 5 on the
objXLBook.ProtectSharing line.

Sub SetSharing()
Dim strFilePathName As String
Dim objXLBook As Excel.Workbook

Set objXLBook = ActiveWorkbook
strFilePathName = "c:New Microsoft Excel Worksheet.xls"

ActiveSheet.Protect Password:="Password", _
DrawingObjects:=True, Contents:=True, Scenarios:=True, _
AllowFiltering:=True
objXLBook.Protect Password:="Password"
objXLBook.ProtectSharing strFilePathName, , , , , "Password"

objXLBook.Save
Set objXLBook = Nothing
End Sub

While code is in break after the error on the objXLBook.ProtectSharing
line, I can go into Excel, click Tools, Protection, Protect and Share
Workbook, check off Sharing with track changes, enter a password twice
AND IT WORKS! Is this a bug that I cannot do this in code?

Any help would be greatly appreciated!
Thanks,
Kristy

I am trying to create a form where people can use the same workbook on a
network at the same time and input data into different parts of the
worksheet. If several people have the workbook open and one person goes to
save, and the second person behind him to save put information into the same
cell, the changes from the other user should be put in a new row below the
original one.
I tried the protect and share and allow multiple users, it works when I have
a simple worksheet, but when I put in the different areas for people to put
in data, it will no longer add the changes onto the original one, it will ask
to accept your changes, or the other persons changes and will replace the
data instead of adding it into a new row.

Hi,
When i am selecting the Format as Table the Protect and share workbook button in Review tab is disable. I want to enable that command button. How it is possible. I can't even unformat that table. Is there any command for unformat the table for enable the Protect and share workbook button.

Hi,

I want to share a workbook that has macros and is on a network. The macros
(amongst other things) will protect and unprotect the sheet but once I make
the workbook shared I lose this functionality.

Can anyone offer any solutions?

--
Andrew

I followed some instructions for locking cells with formulas earlier and that required protecting the worksheet. No big deal. I try to run a simple sort macro and i get an error. Duhh its protected gotta unprotect it first. So i do

my macro code
all fine and dandy i checked this out to make sure it worked and sure did. As soon as i go and hit protect and share workbook
and re-open the work book to test it out one more time for giggles i get an error. its a vba unprotect error? Is there any
easy way around this? If i unprotect and unshare the workbook im good to go.. seems a pain in the butt for a simple sort if
you ask me.

Hi, I desperately need to share a workbook which contains drawing lines and
be able to edit them but as soon as the workbook is shared it won't allow and
changed to drawing objects, anyone any solution???

We would like to share a workbook and it's macros. We have no problem sharing the workbook or the macros.

We are running into the following error: command not available in a shared workbook.

Any help making all commands available would be appreciated.

Thanks,

BB

We are currently sharing a workbook. However, when one person is in the
workbook it gives another person the option of going in as read-only. The
workbook was setup so that more than one person can work in the file and save
changes at the same time. What are we dong wrong?

When sharing a workbook unable to change color of tabs. Is there a solution?

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

Is it possible to share a workbook using 1 PC??

I have 2 people in the office and myself on the road needing access to the
same excel workbook. I do not have a network. I was hoping to set up a
single PC and allow all 3 of us access to excel and make changes.

Is this even possible???

When sharing a workbook unable to change color of tabs. Is there a solution?

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

Dear Experts,

Is it possible to use the userform when it is protected and shared.
I am able to access when it is protected. but unable to access when that protected worksheet is shared.runtime error message " unprotect method sheet class failed" appears it is shared and used.I have already raised this [B]issue in the forum.I understood from the answers that it is not possible.

" My requirement is multiple users should use the form and they should not have the access to edit the entires made by them"

Please give me a solution.

" Being a moderator myself i should only be able to edit or modify the entries made by the users."

Note:
I dont want to create multiple excel sheets for multiple users.i want to use a single excel sheet (Shared and protected)

Also provide me an alterante solutions if any!!

Hia
Im not sure if this is possible, but, i have say 40 files, that will always have the same location, and file name.
I also have an existing macro that opens each file in turn, does a little formatting, and puts a password on them.

However im really eager to add some code that will share each work book before closing.

Is there any way i can do this? I just need the line of code that shares a workbook...

Thanks!

Please give me a VB script code which creates the new excel workbook and shares the workbook?

We are currently sharing a workbook. However, when one person is in the
workbook it gives another person the option of going in as read-only. The
workbook was setup so that more than one person can work in the file and save
changes at the same time. What are we dong wrong?

Hi,

I'm trying to figure out why I cant insert hyperlinks when the
worksheet is protected and shared. But I can insert hyperlinks when I
protect the sheet.???

Any help is much appreciated.

Thanks

--
harpscardiff
------------------------------------------------------------------------
harpscardiff's Profile: http://www.excelforum.com/member.php...o&userid=25960
View this thread: http://www.excelforum.com/showthread...hreadid=505256

Hi

Thanks for reading my post.

I am trying to open and close a workbook from an active workbook but my code has a bug which i can figure out.

Your help is appreciated, thank you.

Sub Import()
Dim wbkOpenClose As Workbook
Dim wksINPUT As Worksheet
Dim wbkCurrentFileLocation As Workbook

Set wbkOpenClose = ActiveWorkbook
Set wksINPUT = wbkOpenClose.Sheets("INPUT")
          
    Set wbkCurrentFileLocation = Workbooks.Open(Filename:=wksINPUT.Range("Current_File_Location").Value)
        
    wbkCurrentFileLocation.Close (False)
           
End Sub


Hi,

I'm trying to figure out why I cant insert hyperlinks when the worksheet is protected and shared. But I can insert hyperlinks when I protect the sheet.???

Any help is much appreciated.

Thanks

Hi im having trouble sharing a workbook with a table.

I get the error saying to convert the table to a range or remove any xml

i have tried doing both to no avail.

any ideas?

Thanks


No luck finding an answer? You could always try Google.