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

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


On Tue, 17 Mar 2009 12:34:02 -0700, ub wrote:

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" wrote:

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

On Tue, 17 Mar 2009 11:29:06 -0700, ub wrote:

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

Hi Gord

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

"Gord Dibben" wrote:

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


On Tue, 17 Mar 2009 12:34:02 -0700, ub wrote:

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" wrote:

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

On Tue, 17 Mar 2009 11:29:06 -0700, ub wrote:

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

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

On Tue, 17 Mar 2009 18:23:01 -0700, ub wrote:

Hi Gord

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

"Gord Dibben" wrote:

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


On Tue, 17 Mar 2009 12:34:02 -0700, ub wrote:

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" wrote:

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

On Tue, 17 Mar 2009 11:29:06 -0700, ub wrote:

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

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

On Wed, 18 Mar 2009 07:46:44 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

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

On Tue, 17 Mar 2009 18:23:01 -0700, ub wrote:

Hi Gord

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

"Gord Dibben" wrote:

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


On Tue, 17 Mar 2009 12:34:02 -0700, ub wrote:

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" wrote:

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

On Tue, 17 Mar 2009 11:29:06 -0700, ub wrote:

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