|
|
 |
Locking cells in Auto filtered list
|
|
On Thu, 3 Jul 2008 01:25:27 -0700 (PDT), Raj <...@gmail.com
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
|
|
|
|
|
On Thu, 3 Jul 2008 09:50:54 +0100, "Nigel" <...@nosupanetspam.com
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
--
Regards,
Nigel
nige...@9sw.co.uk
"Raj" <...@79g2000hsk.googlegroups.com...
|
|
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 <...@gmail.com
|
|
On Thu, 3 Jul 2008 02:23:13 -0700 (PDT), Raj <...@gmail.com
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
|
|
|
|
Latest discussions from this group
(microsoft.public.excel.programming)
|
Selection.EntireRow - Filtered
[09 Oct 2008]
On Thu, 9 Oct 2008 16:22:11 -0700, Bam <Bam@discussions. microsoft.com
Hi,
I am trying...
to copy (selected) filtered data from one sheet to another.
Previously I have used Selection...
|
Function to return two values
[09 Oct 2008]
On Thu, 9 Oct 2008 13:13:00 -0700, Jac Tremblay <jac.tremblay@don otspam.com
Hi,
Is it...
possible to create a function that will return two values, a boolean
and a string?
I need...
|
Determine if a workboo has been saved
[09 Oct 2008]
On Thu, 9 Oct 2008 12:45:02 -0700, QuietMan <QuietMan@discuss ions.microsoft.com
I trying...
to have a macro auto exceute to put the user name in a cell on a
worksheet each time ...
|
| More... |
|
|
|
|
 Omgili Home
Usenet
About
FAQ
Omgili TV Shows
Privacy
International Omgili: Israel | Japan
|