Locking cells in Auto filtered list

[Raj]

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

 

[paul.robinson]

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

 

[Nigel]

Locking the cells is only relevant if the worksheet is then protected.

Locking should not be affected with a filtered list in place (xl2007)

Nor should a filtered list / locked cells affect the Save function.

Post your code as it appears it might be something else causing the problem

 

[Raj]

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










- 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