Protect shared workbook

[ub]

Hi
Can a shared workbook be protect so that no modifcation can be done in the
sheet data.
If yes, what will be VBA code to unprotect the workbook , save user data and
close workbook again in protected mode.

Thanks

 

[Gord Dibben]

Protecting a workbook will not prevent changes in a worksheet.

A worksheet in a workbook can be protected so no changes can be made to that
sheet.

But what would be the reason to save if nothing was changed?

What exactly do you want to achieve?


Gord Dibben MS Excel MVP

 

[ub]

Hi Gord

I have a shared workbook that 5 user share. Each of them feeds the data in
the shared workbook through a user form.
But the user is not aware of the shared workbook and I don't want them to
edit the data in the shared workbook.
I need to write a VBA code , so when user sumits the data, the shared
workbok will be unprotected and when the data is updated, the workbook is
closed with protect mode.

Please advise

 

[Gord Dibben]

As I stated earlier.............protecting the workbook will not prevent
users from editing a worksheet.

You must protect the worksheet

Might be easier to just make the sheet xlveryhidden.

The UserForm data will be transferred to the sheet even when veryhidden.

But you could code into your Workbook_Open or whatever you use to load the
userform


Sheets("Sheet1").Unprotect Password:="justme"
userform.Show
data gets entered and submitted
userform unloads
Sheets("Sheet1").Protect Password:="justme"


Gord

 

[ub]

Hi Gord

But if I make my workbook shared, the option to protect worksheets is grayed
out.
Please advise

 

[Gord Dibben]

Set up the protection before you share the workbook.

You must get everything ready including adding code prior to sharing.

You won't have access to the VBProject after saving as a shared workbook.


Gord

 

[Gord Dibben]

Ignore those instructions. Too early in the morning<g>

You cannot do this on a shared workbook.

I would go with the hidden sheet.


Gord