Returned position address on protected sheet

  • Thread starter Thread starter Bobby
  • Start date Start date
B

Bobby

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
 
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
Click to expand...

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
 
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 positionor 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
Click to expand...

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.
 
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.
Click to expand...

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
 
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
Click to expand...
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!
 
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.
 
Bobby was thinking very hard :
ActiveSheet.EnableSelection = xlUnlockedCells
Click to expand...

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
 
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
 
Back
Top