Weird VB Error Message???

[Escape777]

Hi,

I am having problems with protection of sheets in VB...

I can protect/unprotect sheet no. 3 no problems. As soon as I add the protection on sheet1 I get the following error.

Runtime error '32809'
Application or object-defined error

Now the code does not seem to have any funnies in but it keeps on chrasing at the protection for sheet1....

Here is the code...


'Unprotect sheet


Sheet3.Unprotect Password:="password"

'Record data into Database

Sheet3.Range("a1:j1").Copy
Sheet3.Range("a65000").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


' Clear input sheet

Sheet1.Unprotect Password:="password"

Range("v1").Value = 0
Range("w1").Value = 0
Range("h10:j12").ClearContents
Range("f16:j18").ClearContents
Range("f22:l25").ClearContents
Sheet1.Protect Password:="password"

'Protect sheets
Sheet3.Protect Password:="password"


Any reason it would carsh on 1 but not on 3?

 

[AlphaFrog]

This won't fix your current error but it may fix a different potential problem...

This part of the code...

Sheet1.Unprotect Password:="password"

Range("v1").Value = 0
Range("w1").Value = 0
Range("h10:j12").ClearContents
Range("f16:j18").ClearContents
Range("f22:l25").ClearContents
Sheet1.Protect Password:="password"

That will clear the cells on the currently Active sheet which may or may not be Sheet1. You should change it to something like...

' Clear input sheet
With Sheet1
    .Unprotect Password:="password"

    .Range("v1").Value = 0
    .Range("w1").Value = 0
    .Range("h10:j12").ClearContents
    .Range("f16:j18").ClearContents
    .Range("f22:l25").ClearContents
    .Protect Password:="password"
End With

Come to think of it, it may fix your current problem if the active sheet is not Sheet1 and it is protected.

 

[Escape777]

AlphaFrog,

Sheet1 is indeed the active sheet. Your portion of code has just shifted the error to the "with sheet1" portion...


What potential problem would the code have prevented?

Is there a reason why your code would work if sheet1 was not the active sheet?

I kow how to help myself with some basic code but when things go awry I am usually lost...

Thx

 

[jasonb75]

2 things to check

are you using correct password for sheet1? (assuming real code has differnt passwords for each sheet).

does sheet 1 actually exist? (note that if you deleted the original sheet1 tab, then renamed another sheet1, the VBA index number will be different).

apart from the potential flaw that Alpha pointed out, there is no reason for your code to crash.

 

[AlphaFrog]

What potential problem would the code have prevented?

Is there a reason why your code would work if sheet1 was not the active sheet?

Say Sheet2 was the active sheet when you run the macro.
In your original code, these lines of code...

Range("v1").Value = 0
Range("w1").Value = 0
Range("h10:j12").ClearContents
Range("f16:j18").ClearContents
Range("f22:l25").ClearContents

...don't specify a sheet. So they will work on the active sheet. So if Sheet2 was the active sheet, then your original code would clear Sheet2.

The code I gave you...

[COLOR="Red"]With Sheet1[/COLOR]
    .Unprotect Password:="password"

    .Range("v1").Value = 0
    .Range("w1").Value = 0
    .Range("h10:j12").ClearContents
    .Range("f16:j18").ClearContents
    .Range("f22:l25").ClearContents
    .Protect Password:="password"
[COLOR="Red"]End With[/COLOR]

...specifies Sheet1 as the sheet that each function is to work on even if Sheet1 was not the currently active sheet.

It's just more specific and could potentially fix the problem if sheet1 wasn't the active sheet.

 

[Escape777]

aahhh ok now i see. I has never been a problem seeing that I try and manage my macros to run only form specific locations but I see where it could become a problem. I will do things this way in future...


I still have a problem with the run time error though...

 

[AlphaFrog]

Try what jasonb75 suggested.

1.) double check that the password for Sheet1 is actually "password". Also note that caps matter. e.g. "password" is not the same as "Password"

2. Does Sheet1 actually exist?

 

[Escape777]

I have actually checked that and on both counts I am OK...

 

[jasonb75]

Try commenting out the code lines to unprotect and protect the sheet, then unprotect manually and run the code.

does it run or fail then?

 

[Escape777]

I have tried commenting out the code before. it works without the line, but with it, it crashes... Again Sheet 3 works fine but sheet 1 does not want to work... Leaving the password protection on sheet3 works, no hassles.

I have tried different passwords as well.

Like i said before, this one has me stumped...