Select Cell within range

[ranswrt]

I am try to write a procedure to start a userform when a cell is clicked. I
am using the selection change event in the worksheet. I need to keep the
cells locked to protect the contents in them. Is there a way to protect the
sheet but only allow cells within a range to be selected not all the cell on
the worksheet? Or is there another way to have userform start up by clicking
or double clicking a cell?
Thanks

 

[Rick Rothstein]

You can still protect your worksheet and allow the user to select any cells,
then use SelectChange to decide if the selection was alright or not. Here is
some code that will confine the user's selection to an allowed range; for
example, if they click in the allowed range (D4:H8 is the example range
implemented in my code below), then you can show your UserForm (replace my
MsgBox statement with your own code), otherwise the code below returns the
selection to the top left cell of the AllowedRange.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const AllowedRange As String = "D4:H8"
On Error GoTo OnceOnly
Application.EnableEvents = False
If Not Intersect(Target, Range(AllowedRange)) Is Nothing Then
MsgBox "Range OK - You can call show your UserForm"
Else
With Range(AllowedRange)
Cells(.Row, .Column).Select
End With
End If
OnceOnly:
Application.EnableEvents = True
End Sub

 

[Jim Thomlinson]

When you protect the sheet (assuming you are using XL2002 or better) then you
get a dialog that ask you how you want to protect the sheet. One item is the
ability to select locked cells. If you uncheck that then the user can not
select any cell you have locked.

As for displaying a user form you can either use a button or one of the cell
events. Selection chage can work as you have suggested. That will fire
whether or not the mouse, enter key or arrows are used. you could use the
double click event. In that case (IMO a better option) you can only pop the
form with the mouse...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Target.Address = "$B$2" Then
Cancel = True
UserForm1.Show
End If
End Sub

 

[ranswrt]

Thanks I'll give it try

You can still protect your worksheet and allow the user to select any cells,
then use SelectChange to decide if the selection was alright or not. Here is
some code that will confine the user's selection to an allowed range; for
example, if they click in the allowed range (D4:H8 is the example range
implemented in my code below), then you can show your UserForm (replace my
MsgBox statement with your own code), otherwise the code below returns the
selection to the top left cell of the AllowedRange.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const AllowedRange As String = "D4:H8"
On Error GoTo OnceOnly
Application.EnableEvents = False
If Not Intersect(Target, Range(AllowedRange)) Is Nothing Then
MsgBox "Range OK - You can call show your UserForm"
Else
With Range(AllowedRange)
Cells(.Row, .Column).Select
End With
End If
OnceOnly:
Application.EnableEvents = True
End Sub