is it possible to record a macro that would lock a sheet

[des-sa]

hi again
is there a way to have macro lock a specific sheet for editing when the
macro is run, then maybe have it password protected for editing afterwards
thanks

 

[Bob Phillips]

When a macro runs the sheet is already effectively locked. You can protect
it afterwards with

worksheets("Sheet1").Protect

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Diddy]

Hi Bob,

Following on from that, how would you code it to protect all sheets in the
workbook?

Cheers

 

[Bob Phillips]

Just loop them all

For Each sh In Activeworkbook.Worksheets

sh.Protect
Next sh

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Diddy]

Thank you Bob
--
Deirdre

Just loop them all

For Each sh In Activeworkbook.Worksheets

sh.Protect
Next sh

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[EugeniaP]

This was a very helpful post. I was wondering if it's possible to create a
macro that would protect all the worksheets in the workbook but also allow
users to edit a certain range?

Thanks,
EugeniaP.

 

[Diddy]

Hi EugeniaP,

Once you have unprotected the ranges you want to be able to edit in Format
Cells Protection then you can use the code on this page
http://www.ozgrid.com/VBA/excel-macro-protected-sheet.htm under
UserInterFaceOnly which allows you to run other macros at the same time as
users aer able to edit ranges.

Cheers

 

[EugeniaP]

Hi, Diddy!

I am not sure I get this. Here is my problem. I have 20 sheets in my
workbook that I need to protect but at the same time allow users to edit
range "C2:C30" in each one of these sheets. I don't want to go into every
sheet and manually do "allow users to edit range" 20 times. What would the
macro be to do all at once?

Thanks,
EugeniaP

 

[Diddy]

Hi EugeniaP,

I'm only a newbie so I can only tell you what I would do.

You can probably do this via VBA and if you want to do that it may be better
to post again with a new subject line.

For what it's worth here's what I would do

Hold shift and click on sheets you want to group. On one of the sheets click
on the top left hand corner to highlight the whole of the sheet. Then Format
Cells Protection. I usually clear the locked tick box just to make sure that
all protection is removed then do the same again ticking the locked tick box
to apply protection to all cells. Highlight the range you want un-protected
and Format Cells Protection uncheck box. Then right click on one of the
grouped sheet tabs and choose Ungroup sheets. Then you can protect the sheets
using VBA or manually.

HTH
Diddy