Weird VB Error Message???

Escape777

New Member
Joined
May 6, 2008
Messages
39
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?
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,271
This won't fix your current error but it may fix a different potential problem...

This part of the code...
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...
Code:
' 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

New Member
Joined
May 6, 2008
Messages
39
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

Well-known Member
Joined
Dec 30, 2008
Messages
10,610
Office Version
2019
Platform
Windows
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,271
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...
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...
Code:
[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

New Member
Joined
May 6, 2008
Messages
39
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,271
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?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,610
Office Version
2019
Platform
Windows
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

New Member
Joined
May 6, 2008
Messages
39
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...
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,978
Messages
5,508,532
Members
408,688
Latest member
Bhojraj

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top