LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Lock certain cells from edit- test if select cell name starting wi

Have an excel template where I want to lock some of the fields from user edit.
Manage to write (cut and paste) some code that almost fulfill my demands.
When the user tries to change or delete one cell that is "locked" a popup is
displayed and then the value is set back to the original.
But there some issues I would like to get help with.

1) Instead of counting out the cells that should be locked, as in the code,
I want to use that all those cells have a defined name starting with Z.
If the cell name starting with Z it should not be editable.
How can I do this?
How can I get the name(s) of the selected cell (or cells)?

2) If I fill in any value in a locked cell and press enter or set focus to
another cell the code works as intended. But if I set focus to a textbox
instead of a cell it will not work, the cell is changed to new value.
Do any have an idea how this can be solved?

Code
Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewValue As Variant, OldValue As Variant
Dim Test1(1) As Variant
Dim i As Integer
Dim Cell_locked As Boolean
Cell_locked = False
Test1(0) = "B3"
Test1(1) = "C3"
For i = 0 To 1
If Not Intersect(Target, Range(Test1(i))) Is Nothing Then
Cell_locked = True
End If
Next i
If Cell_locked = True Then
NewValue = Target.Value
Application.EnableEvents = False
Application.Undo
OldValue = Target.Value
MsgBox "You cannot change the contents of this cell.", 16, "Locked
cell"
Target.Value = OldValue
Application.EnableEvents = True
End If
End Sub

Thankful for any help and improvement of the coed.
Daniel
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Select range starting at active cell standard_guy Excel Programming 5 January 5th 09 07:30 PM
Edit within a cell to select parts fatherof5clones Excel Programming 5 June 26th 07 03:53 PM
How do i lock tabs so that only an admin can edit them? Dave_FFM Excel Discussion (Misc queries) 2 April 27th 07 02:00 PM
Select cells below and lock them chrisk Excel Discussion (Misc queries) 0 August 2nd 06 11:55 AM
Lock a cell from others to edit? tb Excel Worksheet Functions 3 June 1st 06 06:07 PM


All times are GMT +1. The time now is 06:28 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"