Run-time error 1004 when sheet is protected

**AJ.** · 05-05-2008, 02:55 AM

Hi All,

I've been "Googling" this all day and can't work this out. I'm really new to VBA and need very basic instructions on how to fix this. From what I've read it's a common problem, I just don't know what to do.

I'm basically trying to sort the data and it works fine when unprotected but I get the error message when protected. When I go to debug, the following is highlighted.

Please Login or Register  to view this content.

Can anyone help please? Thanks.

**mudraker** · 05-05-2008, 04:11 AM

You will need to unprotect your sheet.

This can be done via VBA as long as you know the password

**VBA Noob** · 05-05-2008, 04:12 AM

AJ,

I've moved your post to programming. This link should help you

http://www.ozgrid.com/VBA/excel-macr...cted-sheet.htm

Post back if you still have any questions

VBA Noob

**AJ.** · 05-05-2008, 06:33 PM

Thanks Guys.

VBA Noob, I have read that page over and over yesterday and still can't figure it out. Where do in the VBA script do you put that? I've tried at the start, the end, in the middle! I still can't get it to work.

**erich_7719** · 05-05-2008, 07:44 PM

The unprotect goes before the script that is editing the worksheet.

When the script is done editing the wooksheet. the protect sould be used to reprotect the workshees.

Something like this..

Please Login or Register  to view this content.

Hope this helps.

**AJ.** · 05-05-2008, 08:48 PM

Thanks erich. That makes more sense and I think has almost fixed the issue. However, I'm getting a new error. "Run-time error 9: Script out of range." When I go to debug I get the following highlighted.

Please Login or Register  to view this content.

At the moment, the whole script looks like this.

Please Login or Register  to view this content.

Does this look right?

**erich_7719** · 05-05-2008, 09:25 PM

Please Login or Register  to view this content.

First things first, I myself am prety much just starting out with VBA. So with that said...

Two things could be happening That I can think of...

1. you are using the wrong name (should be in quotes) or variable.

or

2. You have mistyped the password.

This is one of my set-ups (password has been changed for my protection).
simple as this for unprotection.

Please Login or Register  to view this content.

And to protect

Please Login or Register  to view this content.

Password - states the password
DrawingObjects - protects any objects
Contents - protects locked cells
Scenarios - not to sure what these are but I left it in
UserInterfaceOnly - will not alow VBA to select a cell but VBA alown can edit the cells.

something you could do is

Please Login or Register  to view this content.

**AJ.** · 05-05-2008, 09:59 PM

Thanks for that erich. It looks like it has worked!

ok, I think the rules of this forum state that I've got to say how you helped or how I fixed it.

1. You told/taught me that I have to use the code

Please Login or Register  to view this content.

2. You also taught me that I have to split the code. i.e.

Please Login or Register  to view this content.

at the start and

Please Login or Register  to view this content.

at the end.

3. I had also left out the " " around the name of my sheet and my password in the script. Once I added the " " marks it fixed it up.

Thanks very much!!

**erich_7719** · 05-05-2008, 10:33 PM

Glad I could help.

Run-time error 1004 when sheet is protected

LinkBack

Thread Tools

Rate This Thread

Display

Run-time error 1004 when sheet is protected

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions