Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
auto locking cells after new entry/edit | Setting up and Configuration of Excel | |||
Locking cells in Auto filtered list | Excel Programming | |||
Conditional List (auto filtered) | Excel Discussion (Misc queries) | |||
loop thru auto-filtered list | Excel Programming | |||
Cells.Find in Auto-Filtered range | Excel Programming |