Protected cells 3

[is1ma1il]

I want to protect a sheet in excel but also in a macro I copy the sheet and move in down a row to leave row 1 empty. It does not seem to like this as it is protected any help?

 

[Zelandakh]

you need to protect the sheet first.

 

[xlbo]

Use activesheet.unprotect or
sheets("Sheetname&quot.unprotect

then your code

then activesheet.protect or
or sheets("Sheetname&quot.protect

HTH Rgds
~Geoff~

 

[rmikesmith]

Geoff,

Thought I'd pass along something I discovered very recently (Hey, I've only been using Excel for about 10 years ). If you use something like

ActiveSheet.Protect UserInterfaceOnly:=True

, say in the Workbook_Open event procedure, then your code can make changes to the worksheet without unprotecting first. This option does not appear to be available through the Menu (at least in Excel 97).

Regards,
Mike

 

[SQLScholar]

Mike:- Great piece of code there... i will have to try that next time i need to do that. Last time i was mucking about constantly protecting and unprotecting.

Nice one

Dan

 

[is1ma1il]

thanks guys

 

[xlbo]

Mike - genius - another gem and another star
How the f%&k did you figure that one out ???
I'm VERY impressed - gonna have to re-jig some of my projects now
Can you confirm that if you do this, you can perform all normal actions through VBA without falling over ?? Rgds
~Geoff~

 

[rmikesmith]

Geoff,

Thank you much. Honestly, I was attempting to reply to another Tek-Tips post (thread68-348000) and re-visited the documentation for the Protect method (when all else fails, rtfm) and stumbled upon it (so much for genius). It certainly seemed worth a try; I also have a number of apps where I unprotect/protect as needed. I have not tested this exhaustively to see if all VBA actions are permitted. Perhaps if you re-work some of your projects you can let us know if there's any falling over fallout (can you say guinea pig?).

Regards,
Mike

 

[faxof]

mike, you the king! star!

 

[Zelandakh]

Mike - that should be an FAQ! Simple and brilliant.