Returned position address on protected sheet

[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

 

[GS]

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

 

[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 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

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]

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

 

[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

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!

 

[Ben McClave]

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.

 

[GS]

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

 

[GS]

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