protected sheet not being able to save

royhern

Board Regular
Joined
Feb 21, 2002
Messages
158
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...
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Dragracer

Board Regular
Joined
Jun 9, 2002
Messages
151
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"
 

Forum statistics

Threads
1,144,737
Messages
5,726,000
Members
422,652
Latest member
Elnene1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top