worksheet protection
 

Hi

I know how to password protect a worksheet - but is it possible to not have
to keep re-protecting the sheet after I have made changes.

Spreadsheet will be saved on a shared drive - but not a shared workbook. I
want to be able to unhide columns and make changes then re hide these columns
and save - but don't want to have to keep protecting the worksheet/workbook.

Any ideas? (maybe a simple macro if no obvious way)

Thanks

worksheet protection
 

Peter,

Do it using the before close event


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Sheet1").Protect Password:="Mypass"
End Sub

Mike

"Peter" wrote:

Hi

I know how to password protect a worksheet - but is it possible to not have
to keep re-protecting the sheet after I have made changes.

Spreadsheet will be saved on a shared drive - but not a shared workbook. I
want to be able to unhide columns and make changes then re hide these columns
and save - but don't want to have to keep protecting the worksheet/workbook.

Any ideas? (maybe a simple macro if no obvious way)

Thanks

worksheet protection
 

Hi Mike

Thanks for this - it works wonders, I was trying to add an extra step before
saving the spreadsheet and that is to hide the specific columns - using the
following:

Sheets("Sheet1").Columns("D:H").Select
Selection.EntireColumn.Hidden = True

this works providing I have unprotected the sheet and left the columns
unhidden - if I open the sheet and close it without changes then I get a
run-time error (obviously it is trying to hide columns that are already
hidden).

Is there away round this?

"Mike H" wrote:

Peter,

Do it using the before close event


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Sheet1").Protect Password:="Mypass"
End Sub

Mike

"Peter" wrote:

Hi

I know how to password protect a worksheet - but is it possible to not have
to keep re-protecting the sheet after I have made changes.

Spreadsheet will be saved on a shared drive - but not a shared workbook. I
want to be able to unhide columns and make changes then re hide these columns
and save - but don't want to have to keep protecting the worksheet/workbook.

Any ideas? (maybe a simple macro if no obvious way)

Thanks

worksheet protection
 

Peter,

Unprotect it first, it won't throw an error if it's already unprotected

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Sheet1").Unprotect Password:="Mypass"
Sheets("Sheet1").Columns("D:H").Select
Selection.EntireColumn.Hidden = True
Sheets("Sheet1").Protect Password:="Mypass"
End Sub

Mike

"Peter" wrote:

Hi Mike

Thanks for this - it works wonders, I was trying to add an extra step before
saving the spreadsheet and that is to hide the specific columns - using the
following:

Sheets("Sheet1").Columns("D:H").Select
Selection.EntireColumn.Hidden = True

this works providing I have unprotected the sheet and left the columns
unhidden - if I open the sheet and close it without changes then I get a
run-time error (obviously it is trying to hide columns that are already
hidden).

Is there away round this?

"Mike H" wrote:

Peter,

Do it using the before close event


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Sheet1").Protect Password:="Mypass"
End Sub

Mike

"Peter" wrote:

Hi

I know how to password protect a worksheet - but is it possible to not have
to keep re-protecting the sheet after I have made changes.

Spreadsheet will be saved on a shared drive - but not a shared workbook. I
want to be able to unhide columns and make changes then re hide these columns
and save - but don't want to have to keep protecting the worksheet/workbook.

Any ideas? (maybe a simple macro if no obvious way)

Thanks

worksheet protection
 

Mike,

You make it look so simple - you have been a great help

Thanks


"Mike H" wrote:

Peter,

Unprotect it first, it won't throw an error if it's already unprotected

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Sheet1").Unprotect Password:="Mypass"
Sheets("Sheet1").Columns("D:H").Select
Selection.EntireColumn.Hidden = True
Sheets("Sheet1").Protect Password:="Mypass"
End Sub

Mike

"Peter" wrote:

Hi Mike

Thanks for this - it works wonders, I was trying to add an extra step before
saving the spreadsheet and that is to hide the specific columns - using the
following:

Sheets("Sheet1").Columns("D:H").Select
Selection.EntireColumn.Hidden = True

this works providing I have unprotected the sheet and left the columns
unhidden - if I open the sheet and close it without changes then I get a
run-time error (obviously it is trying to hide columns that are already
hidden).

Is there away round this?

"Mike H" wrote:

Peter,

Do it using the before close event


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Sheet1").Protect Password:="Mypass"
End Sub

Mike

"Peter" wrote:

Hi

I know how to password protect a worksheet - but is it possible to not have
to keep re-protecting the sheet after I have made changes.

Spreadsheet will be saved on a shared drive - but not a shared workbook. I
want to be able to unhide columns and make changes then re hide these columns
and save - but don't want to have to keep protecting the worksheet/workbook.

Any ideas? (maybe a simple macro if no obvious way)

Thanks