Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Select range starting at active cell | Excel Programming | |||
Edit within a cell to select parts | Excel Programming | |||
How do i lock tabs so that only an admin can edit them? | Excel Discussion (Misc queries) | |||
Select cells below and lock them | Excel Discussion (Misc queries) | |||
Lock a cell from others to edit? | Excel Worksheet Functions |