Deny/Allow for certain range.

D

Dan

Hello everyone,

I have a question and I hope to get an answer. I use a Excel spreadsheet and
I need to set it up the way that only specific range in the sheet can be used
as an active. There is range such as B1:C20, so whenever mouse is being
clicked out of that range it should not select any other cell, except for
those in the specified range. How can that be accomplished?
 
B

Bill Kuunders

one way


enter the code below into the worksheet selection change
note ..........top and bottom lines are already there

method to get there

right click onto the sheet tab
select view code
select worksheet in the dropdown box which shows "general"
select selectionchange in the righthand dropdown box

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Action_Range As Range
Set Action_Range = Range("B1:C20")

Dim Current_Range As Range
Set Current_Range = ActiveCell


If Intersect(Current_Range, Action_Range) Is Nothing Then

Range("B1").Select
Beep
MsgBox "You need to select within B1 to C20", vbInformation

End If
End Sub
 
T

T. Valko

You can do this by applying sheet protection

Select the *entire* sheet by clicking the little square immediately to the
left of column A and immediately above row 1.

Goto the menu Format>Cells>Protection tab>Locked>OK
Select the range of cells that you want to allow access to B1:C20
Goto the menu Format>Cells>Protection tab>uncheck Locked>OK
Now, set the sheet protection:
Goto the menu Tools>Protection>Protect sheet
You'll see various options available to you. Since you don't want users to
be able to select cells outside of the range B1:C20 uuncheck the option:
Select locked cells and check the option: Select unlocked cells

OK out
 
Top