Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deny/Allow for certain range.
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deny/Allow for certain range.
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 -- Greetings from New Zealand "Dan" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deny/Allow for certain range.
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 FormatCellsProtection tabLockedOK Select the range of cells that you want to allow access to B1:C20 Goto the menu FormatCellsProtection tabuncheck LockedOK Now, set the sheet protection: Goto the menu ToolsProtectionProtect 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 -- Biff Microsoft Excel MVP "Dan" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
deny printing for certain blank cells | Excel Worksheet Functions | |||
Excel Addin:Setting the range to the Excel.Range object range prop | Excel Worksheet Functions | |||
formula to sort a range so that it matches the exact rows of a column that is outside that range? | Excel Discussion (Misc queries) | |||
How do I deny opening and viewing any file to any user and lock i. | Excel Discussion (Misc queries) | |||
Deny file access if user declines macros? | Excel Discussion (Misc queries) |