Hi I have a spreadsheet with various code and I am trying to lock certain parts of the spreadsheet to ensure nothing gets changed by accident. However when i drop down a menu (part of a VLOOKUP function) it come up with the error in the subject line and point the 'debug' as below:
Runtime Error 1004.JPG
@frogboy
based on the error description, your sheet is currently protected.
try to add to add this code before the "set" code and on the last line consecutively,
sheet1.unprotect password:="your password"
sheet1.protect password:="your password"
Regards,
thisisgerald
Don't forget to mark your thread as [SOLVED].
Hi thisisgerald, I have tried this but it comes up with the following error and points the debug at the "password"
(which is my password)
"Compile Error - Invalid outside Procedure"
Hi thisisgerald, I have tried this but it comes up with the following error and points the debug at the "password"
(which is my password)
"Compile Error - Invalid outside Procedure"
can you attach your sample workbook instead?
unfortunately it contains a lot of sensitive data, and would take too much time to remove and then repost.
instead I have attached the full VBA script (albeit with a few things edited), hope this helps?
Please Login or Register to view this content.
@frogboy,
disregard my first suggestion, i found out that it cannot unprotect the sheet since the code is on worksheet change.
kindly try this one, add these:
Then close your file then open it again.Please Login or Register to view this content.
Your new code will be like this:
Please Login or Register to view this content.
Last edited by thisisgerald; 12-03-2012 at 07:25 AM.
Unfortunately it hasnt made any difference and comes up with the same error message. cheers
on what part you are encountering error? have you tried the code below on post #8?
yes, tried the code and comes up with the original error message. I have added the code, saved and closed the spreadsheet and then reopened it without any difference. cheers
Bump .... anyone??
@frogboy
which part are you having run-time error?
this code should be under "ThisWorkbook".
while this one is in your active sheet:Please Login or Register to view this content.
Please Login or Register to view this content.
excellent, that worked. Apologies for being a newb!!
Thanks
... in fact sorry it didnt work. If I protect the sheet and save and close and then re-open it the pages is not protected. It appears it loses its protection on closing? Probably something else I have missed? Please advise? cheers
... not an expert but reading the code it says to protect on closing and unprotect on opening. However I want to stop people being able to mess with formulas when they are in the spreadsheet so this appears to be defeating the object of originally protecing the sheet?
@frogboy,
maybe you can start recording macros on protecting worksheet.
I'm not sure if the macro unprotected then protect the worksheet, the default options will be applied.
regards,
thisisgerald
sorry I dont understand what you mean?
Are there any other thoughts on this?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks