protected sheet not being able to save

[royhern]

Well still have not been able to resolve this issue but i am hoping someone can look at this and come up with a solution.

this is the formula on the distination cell(s).

=IF(LEN(DATA!A7),DATA!A7,"empty")

This is the code i am using.

Private Sub Worksheet_Calculate()
Dim cell As Range
ActiveSheet.Unprotect
For Each cell In Range("A5:A69")
If cell = "empty" Then
cell.EntireRow.Hidden = True
Else
If cell.EntireRow.Hidden Then cell.EntireRow.Hidden = False
End If
Next
ActiveSheet.Protect
End Sub


This is the problem i am trying to fix:

1) When sheet/workbook is protected and try to save the workbook under another name i get a debug error. "cell.EntireRow.Hidden = True" is highlighted and unable to set hidden property of the range class

2) also it asks for a password to unprotect

3) I have no problems saving with same name.

Any ideas team...

 

[Dragracer]

Try changing your "ActiveSheet.unprotect" to "Me.unprotect" and the protect command too. With no quotes of course.

Your problem is when you change a value on sheet "Data" your forumla updates (which is not in your active sheet)and causes the worksheet_calulate to trigger. The same holds true for saving if the sheet which house the calculate event is not active it will error out.

If your sheet was not saved with a password it should not ask for one to unprotect. You could at the password to your macro if you wanted. use this.

Me.Protect "YourPassword", DrawingObjects:=True, _
Contents:=True, Scenarios:=True

Me.Unprotect "YourPassword"