Locking cells in Auto filtered list
 

Hi
Still don't get a problem. Your called macro works fine for me with
filtering on or off. You mention another macro...as I said, always
helps to see all the code.
some (very) long shot thoughts are
1. The two macros have different passwords???
2. Try Dim password as String
3. Try mypassword instead of password. Password is a keyword within
the protect method & protect might get flaky with you using password
as a variable name.

regards
Paul

On Jul 3, 10:23*am, Raj wrote:
On Jul 3, 1:45*pm, wrote:





Hi
I tested this in Excel 2003

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Worksheets("InputDetails").Cells.Locked = True
End Sub

it locked the cells OK whether a list in column A was filtered or not.
VBA doesn't always hit the right line when an error occurs, so perhaps
there is another problem with your code.
Always helps to see the whole code.

regards
Paul

On Jul 3, 9:25*am, Raj wrote:

Hi,

I have list in worksheet that is enabled for autofilter.

The before save event of the workbook has code to protect the sheet
when saved. This is prevent further data entry until the user invokes
a macro to unprotect the sheet.

The protect sheet macro has the following code:
Worksheets("InputDetails").Cells.Locked = True

All is well as long as the worksheet is unfiltered and saved. But when
the list is filtered and a save attempted, the code fails and the line
above is highlighted. Obviously, it is failing to lock cells in
filtered lists.

Any solutions for this please?

Thanks in advance for the help.

Regards,
Raj- Hide quoted text -

- Show quoted text -

Hi,

This is the code that is called from the Workbook before save event:

Public Sub rspProtectInputSheet()
Dim password * *'This line of code is optional
password = "xxxx"
Worksheets("InputDetails").Cells.Locked = True
Worksheets("InputDetails").Protect password, contents:=True,
Userinterfaceonly:=True, AllowFormattingColumns:=False
Worksheets("ProjectDetails").Cells.Locked = True
Worksheets("ProjectDetails").Protect password, contents:=True,
Userinterfaceonly:=True, AllowFormattingColumns:=False
End Sub

The purpose of the code is to lock all cells in the two worksheets
referred therein. When the user uses another macro, cells in a range
where input is to be allowed are unlocked and the sheet is protected
again.

The code works smoothly when the list in InputDetails is not filtered.
It fails when the sheet is filtered and highlights the
"Worksheets("InputDetails").Cells.Locked = True"

I am using an .xls (97-2003) workbook in Excel 2007.

Thanks and Regards,
Raj- Hide quoted text -

- Show quoted text -