Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making cells so users can select them
Hi
I need to make a group of cells protected in such a way that the users cannot even select them. I tried to lock them and then protected the worksheet, but the users can still select the cells. The reason I'm trying to do this is to keep users from copying the cells to another location. These cells are borders and if they copy them by mitake it places the borders in another part of the worksheet. Is there a way to make those cells where a user cannot even select them? Thanks so much! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making cells so users can select them
Thanks, I knew there had to be a better way.
Doug "steve" wrote in message ... Doug, Include this in your event code ActiveSheet.EnableSelection = xlUnlockedCells Now they can only selected unlocked cells -- sb "Doug Glancy" wrote in message ... You could try the Worksheet_SelectionChange event. I imagine this will cause other headaches, but I think it does what you want. Right-click on the spreadsheet tab, choose Properties and paste this code: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim badrange As Range Set badrange = Range("A1:A5") 'insert correct range here If Not Intersect(Target, badrange) Is Nothing Then MsgBox "You can't select cells " & badrange.Address ActiveCell.Offset(0, 1).Select 'or select another range End If End Sub Doug "KimberlyC" wrote in message ... Hi I need to make a group of cells protected in such a way that the users cannot even select them. I tried to lock them and then protected the worksheet, but the users can still select the cells. The reason I'm trying to do this is to keep users from copying the cells to another location. These cells are borders and if they copy them by mitake it places the borders in another part of the worksheet. Is there a way to make those cells where a user cannot even select them? Thanks so much! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making cells so users can select them
Kimberly,
You can use the below macro to protect all the worksheets. Replace wxyz with what ever password you want. Be sure to protect the VB project (while in VBE go to Tools VBA Project Properties. Save and close. Now when the workbook opens - only unprotected cells can be selected. BUT - nothing is totally secure. Any one with determination can find a way to copy... ========================================== Private Sub Workbook_Open() Dim WS As Worksheet For Each WS In ThisWorkbook.Worksheets WS.Unprotect Password:="wxyz" WS.EnableSelection = xlUnlockedCells WS.Protect Password:="wxyz", UserInterfaceOnly:=True Next End Sub -- sb "Bob Umlas" wrote in message ... IN the Selection_Change event for the sheet, you can test for specific cells as the target and if selected redirect them back to cell A1, perhaps. For example: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Application.Intersect(Target, Range("MyRange")) Is Nothing Then Range("A1").Select End Sub And MyRange is all the cells you don't want selected. Bob Umlas Excel MVP "KimberlyC" wrote in message ... Hi I need to make a group of cells protected in such a way that the users cannot even select them. I tried to lock them and then protected the worksheet, but the users can still select the cells. The reason I'm trying to do this is to keep users from copying the cells to another location. These cells are borders and if they copy them by mitake it places the borders in another part of the worksheet. Is there a way to make those cells where a user cannot even select them? Thanks so much! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel making it so no changes can be made by other users | Excel Discussion (Misc queries) | |||
Any ideas for making this graph type easy for other users? | Charts and Charting in Excel | |||
Select a range of data dependant on the users input ie dates | Excel Discussion (Misc queries) | |||
Making changes to the same worksheet by two users at the same time | Excel Discussion (Misc queries) | |||
Protect shouldn't default to "allow users to select locked cells" | Setting up and Configuration of Excel |