Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Protected cells 3

Status
Not open for further replies.

is1ma1il

MIS
Feb 15, 2002
51
US
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?
 
Use activesheet.unprotect or
sheets("Sheetname").unprotect

then your code

then activesheet.protect or
or sheets("Sheetname").protect

HTH Rgds
~Geoff~
 
Geoff,

Thought I'd pass along something I discovered very recently (Hey, I've only been using Excel for about 10 years [wink]). If you use something like
Code:
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
 
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
 
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~
 
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?). [wink]

Regards,
Mike
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top