PDA

View Full Version : Solved: Making a Range non-editable



Orange_Wed
08-13-2008, 03:22 PM
Hello all,

I'm in the process of trying to lock down a specific range of cells so that users cannot edit the contents. I have tried locking the range using the standard sheet protection in Excel and that works *except* for when a user needs to insert a row into the sheet.

I have attempted using the Worksheet_SelectionChange event to detect when a user selects a cell within the range, divert the selection a column to the right, and pop a message box indicating that the user cannot edit the cell selected. Once again, I have done not, as my attempt failed.

The range I am trying to lock down is F10:F102, and I need to be able to insert and delete rows, and manipulate the data contained in the range with a macro but I do not want users able to edit the information manually.

Can anyone give me some pointers? Maybe a smidge of code? This is the final result of my research, and it does absolutely nothing.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$F$10,$F$102" Then
Target.Offset(, 1).Select
MsgBox "Cannot Edit Cell"
End If
End Sub

Any help is most appreciated.

Wednesday

Orange_Wed
08-13-2008, 03:42 PM
Found a similar thread (exactly the same, actually) with some suggestions, such as using data validation and a snippet of code that looks similar to mine.

http://www.vbaexpress.com/forum/showthread.php?t=21083

Neither are within my needs at the moment. The code does what it should, but it doesn't allow the user to insert a row. The validation method doesn't keep the user from chaning the data, it just warns that they shouldn't.

Any recommendations?

Orange_Wed
08-13-2008, 03:46 PM
I discovered that the MD's code from the other thread does indeed work, it just gives a code error box when you select an entire row to insert. I can click on the End button and it will then allow me to select Insert from the context menu.

Any ideas on how to either supress this error box or work around the issue of not being able to select an entire row to insert or delete it?

mikerickson
08-13-2008, 06:00 PM
Suppose you "lock down" F10:F102.
The user selects all of row 91 and presses INSERT

Do you now want F10:F103 (old locked cells, plus one from the inserted row) locked down

or the cells F10:F90, F92:F103 (old locked cells, accounting for new addresses; inserted cells are free to change)

or the cells with the address F10:F102 (i.e. inserting kicks the oldF102 out of the "locked" range)?

Orange_Wed
08-14-2008, 08:28 AM
For this application, the macro doesn't have to change the number of locked cells, if a user adds 10 rows, the range of locked cells can still be F10:102. The sheet will only use 2/3 of this range at a maximum, but I like to work with redundancies, just in case.

Thanks kindly for your fast resoponse!

david000
08-14-2008, 01:51 PM
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Set Target = ActiveCell
If Intersect(Target, Me.Range("F10:F102")) Is Nothing Then
Exit Sub
Else

Application.EnableEvents = False
Target.Offset(, 1).Select
MsgBox "Ouch!"
Application.EnableEvents = True

End If
End Sub

Orange_Wed
08-14-2008, 03:56 PM
That's exactly it! Works like a charm. I feel silly for having spent the last four days working on this to no avail now. Which is good.

Thank you very much david000, you saved me weeks of muddling around!