more help with formula

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

I have solved the protection problem. Now the problem i
have is a password problem. On the following formula

Private Sub Workbook_Open
Worksheets("sheet1").Unprotect
With Worksheets("sheet1").Range("A1")
.Value = Value + 1
End With
Worksheets("Sheet1").Protect Secenarios =
TrueUserInterfaceOnly = True
End Sub

When I open the file I get a password box. When I cancel
and go to debug the second line of the formula is
highlighted. What can I do to get around this problem.
I didn't input any password so when I hit ok on the
password box it says invailid password.
Any help would be appreciated.
 
Hi Brian

it would be easier if you stay in the same thread to collect all your
answers an to follow up your issues :-)
Question: Did you eventually protect your worksheet manually with a
password??. If yes, Worksheets("...").Unprotect expects a password.
Try the following:
- After opening the sheet and getting into the Debug mode try to
manually unprotect your spreadsheet ('Tools - Protection')
- Save the file unprotected and try again

With an unprotected worksheet the code below works fine for me
Frank
 
If no password is entered then the worksheet object is not available to
edit. That is probably why the formula is failing.

You should test the protected status of the workbook after the
..unprotect method.
You could do thsi with an On Error trap and ensure that you set on
error status back after the test success or failure.

Like

Private Sub Workbook_Open
Worksheets("sheet1").Unprotect
With Worksheets("sheet1").Range("A1")
On error goto Protected
..Value = Value + 1
Protected
on error goto 0
End With
Worksheets("Sheet1").Protect Secenarios =
TrueUserInterfaceOnly = True
End Sub

Also the code you pasted does not have a '.' qualifier in the line
".Value = Value + 1", this too may be cauisng an error.
 
Back
Top