Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protect certain cells
I would like to know, if there is any other way to protect some cell than
using the Cell.Locked property and setting the .Proptect peroperty for the sheet. Protecting the whole sheet is not really sufficient for me, because you are not allowed to use some nice features on a protecte sheet (like the nice AutoFilter-function). Thanks Thorsten |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protect certain cells
This can not be done, by setting some properties in excel, and you will need to look for a work around. One of the possible work arround could be writing a code in that Worksheet_SelectionChange event procedure. Just for example, if the cells you want to protect are in say column E then Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.EnableCancelKey = xlDisabled If Target.Column = 5 Then Target.Offset(0, 1).Select Msgbox "Please leave that cell alone." End If Application.EnableCancelKey = xlInterrupt End Sub You can accrodingly modified the code for cells you want to protect. Sharad *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protect certain cells
Hi Thorsten,
Protecting the whole sheet is not really sufficient for me, because you are not allowed to use some nice features on a protecte sheet (like the nice AutoFilter-function). You can apply protection and enable the AutoFilter with: With ActiveSheet .EnableAutoFilter = True .Protect userinterfaceonly:=True, _ password:="YourPassword" End With As the UserInterfaceOnly setting is not persistent between sessions, the code should be included in the Workbook_Open or Auto_Open procedures. --- Regards, Norman "Thorsten Walenzyk" wrote in message ... I would like to know, if there is any other way to protect some cell than using the Cell.Locked property and setting the .Proptect peroperty for the sheet. Protecting the whole sheet is not really sufficient for me, because you are not allowed to use some nice features on a protecte sheet (like the nice AutoFilter-function). Thanks Thorsten |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protect certain cells
This seems to work for me.
It would be nice to just unselect the selection in case of a 'locked' cell. Is that is possible??? "Sharad" wrote in message ... This can not be done, by setting some properties in excel, and you will need to look for a work around. One of the possible work arround could be writing a code in that Worksheet_SelectionChange event procedure. Just for example, if the cells you want to protect are in say column E then Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.EnableCancelKey = xlDisabled If Target.Column = 5 Then Target.Offset(0, 1).Select Msgbox "Please leave that cell alone." End If Application.EnableCancelKey = xlInterrupt End Sub You can accrodingly modified the code for cells you want to protect. Sharad *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protect certain cells
If the user disables macros, it will leave your cells unprotected anyway.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Static OldRange as Range Dim bLocked as Boolean on Error goto ErrHandler if OldRange is nothing then Set OldRange = Range("A1") End if Application.EnableCancelKey = xlDisabled bLocked = False for each cell in Target if cell.Locked then bLocked = True exit for end if Next If bLocked Then Application.EnableEvents = False OldRange.Select Else set OldRange = Target End If ErrHandler: Application.EnableCancelKey = xlInterrupt Application.enableEvents = True End Sub -- Regards, Tom Ogilvy "Thorsten Walenzyk" wrote in message ... This seems to work for me. It would be nice to just unselect the selection in case of a 'locked' cell. Is that is possible??? "Sharad" wrote in message ... This can not be done, by setting some properties in excel, and you will need to look for a work around. One of the possible work arround could be writing a code in that Worksheet_SelectionChange event procedure. Just for example, if the cells you want to protect are in say column E then Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.EnableCancelKey = xlDisabled If Target.Column = 5 Then Target.Offset(0, 1).Select Msgbox "Please leave that cell alone." End If Application.EnableCancelKey = xlInterrupt End Sub You can accrodingly modified the code for cells you want to protect. Sharad *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protect Cells | Excel Discussion (Misc queries) | |||
protect cells in excel, but also allow a search in those cells? | Excel Worksheet Functions | |||
How do I protect cells or a range of cells in excel spreadsheet? | Excel Worksheet Functions | |||
about protect just cells | Excel Discussion (Misc queries) | |||
Protect cells | Excel Programming |