Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returned position address on protected sheet
I have a protected sheet with unprotected cells. If the user Click on a protected cell, the returned target is always the first unlock cell position or address. How can I verify that the target value is lock? Here is the code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("E1:F" & lastrow)) Is Nothing Then something..... End If End Sub Thank's ahead |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returned position address on protected sheet
Bobby explained :
I have a protected sheet with unprotected cells. If the user Click on a protected cell, the returned target is always the first unlock cell position or address. How can I verify that the target value is lock? Here is the code: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("E1:F" & lastrow)) Is Nothing Then something..... End If End Sub Thank's ahead Is there any reason why you can't set the protection to allow selecting unlocked cells only? OR, do you need to allow selecting locked cells for some reason? -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returned position address on protected sheet
On Tuesday, November 6, 2012 10:07:25 AM UTC-5, Bobby wrote:
I have a protected sheet with unprotected cells. If the user Click on a protected cell, the returned target is always the first unlock cell position or address. How can I verify that the target value is lock? Here is the code: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("E1:F" & lastrow)) Is Nothing Then something..... End If End Sub Thank's ahead GS I have 2 columns that are unlock Range("E1:F32") for selection purpose(by double clicking). Everything else is locked and protected. If the User Double click in a protected area for some reason the Range("E1:F1") is selected and return to my module for analysis. My point is how can I control that situation. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returned position address on protected sheet
on 06/11/2012, Bobby supposed :
On Tuesday, November 6, 2012 10:07:25 AM UTC-5, Bobby wrote: I have a protected sheet with unprotected cells. If the user Click on a protected cell, the returned target is always the first unlock cell position or address. How can I verify that the target value is lock? Here is the code: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("E1:F" & lastrow)) Is Nothing Then something..... End If End Sub Thank's ahead GS I have 2 columns that are unlock Range("E1:F32") for selection purpose(by double clicking). Everything else is locked and protected. If the User Double click in a protected area for some reason the Range("E1:F1") is selected and return to my module for analysis. My point is how can I control that situation. When you set protection on the sheet, make sure the checkbox that allows users to select locked cells is checked. That way, when they double click on a locked area they get notification that the sheet is protected. Thus, only unlocked cells can be selected. If you leave the checkbox empty then a double click anywhere will cause the active cell to go into EditMode. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returned position address on protected sheet
On Tuesday, November 6, 2012 10:07:25 AM UTC-5, Bobby wrote:
I have a protected sheet with unprotected cells. If the user Click on a protected cell, the returned target is always the first unlock cell position or address. How can I verify that the target value is lock? Here is the code: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("E1:F" & lastrow)) Is Nothing Then something..... End If End Sub Thank's ahead GS this is what I do: Cells.Locked = True ActiveSheet.Range("E1:F1").Select Range("E1:F" & lastrow).Locked = False ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, UserInterfaceOnly:=True ActiveSheet.EnableSelection = xlUnlockedCells I am not sure that I understand your suggestion. Do I have to add something to my code? Regards! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returned position address on protected sheet
Bobby,
The line: "ActiveSheet.Range("E1:F1").Select" is probably the reason that your macro returns the range "E1:F1". That line is not necessary for your code to work, so you might try removing it. If you do need to select cells for some reason, then you might want to add the line: "Application.EnableEvents = False" at the beginning of your macro and "Application.EnableEvents = True" at the end of it so that the "Private Sub Worksheet_SelectionChange(ByVal Target As Range)" event is not triggered. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returned position address on protected sheet
Bobby was thinking very hard :
ActiveSheet.EnableSelection = xlUnlockedCells Change the above to... ActiveSheet.EnableSelection = xlNoRestrictions ...so if users double click anywhere outside your unlocked cells they'll be notified that EditMode is not available for the selected cell. After a few times they'll learn (hopefully) to only double click your unlocked cells.<g -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returned position address on protected sheet
Ben's suggestion makes sense because you only need to reference a range
to act on it, *and* in this case it retriggers the event. The way you had the EnableSelection set in your code sample, it puts ActiveCell into EditMode whenever a user double clicks outside the unlocked area. So... Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Intersect(Target, Range("E1:F" & lastrow)) Is Nothing Then Exit Sub With ActiveSheet .Cells.Locked = True .Range("E1:F" & lastrow).Locked = False .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, _ UserInterfaceOnly:=True .EnableSelection = xlNoRestrictions End With End Sub -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Position Cursor to same address on another tab | Excel Worksheet Functions | |||
Position of cursor when protected file is opened | Excel Worksheet Functions | |||
Be able to edit position of datalabels in a protected chart. | Excel Programming | |||
be able to edit datalabel position of all series in a protected ch | Excel Programming | |||
Address returned, Not Contents? | Excel Discussion (Misc queries) |