Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Hi, I have a shared excel file. I want to prevent users from deleting and
changing entries on it. I only want them to be able to add new data to the empty cells. I found a sample code to do that, but whenever I open the file , i need to re-run the macro to protect the cells that are not empty. Sample Code : Private Sub Worksheet_Change(ByVal Target As Range) Dim cel As Range If Not Intersect(Target, Me.[a1:ba2000]) Is Nothing Then Application.EnableEvents = False On Error Resume Next Me.Unprotect "pw" On Error GoTo 0 For Each cel In Intersect(Target, Me.[a1:ba2000]).Cells cel.Locked = True Next ActiveSheet.EnableSelection = xlUnlockedCells Me.Protect "pw" Application.EnableEvents = True End If End Sub Sub ini_lock() Dim cel As Range Application.EnableEvents = False On Error Resume Next ActiveSheet.Unprotect "pw" On Error GoTo 0 Cells.Locked = False For Each cel In Intersect([a1:ba2000], [a1:ba2000]) If cel.Value < "" Or cel.HasFormula = True Then cel.Locked = True Next ActiveSheet.EnableSelection = xlUnlockedCells ActiveSheet.Protect "pw" Application.EnableEvents = True End Sub How can I make this macro automatically run , when I open the file? I tried this code, but I still need to go to the macros menu and run macro manually. Private Sub Workbook_Open() Run ini_lock End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Can I lock a cell after data entry so entry can not be changed | Excel Discussion (Misc queries) | |||
Lock format but allow data entry | Excel Discussion (Misc queries) | |||
lock cells after entry | Excel Programming | |||
Lock Row after Data Entry | Excel Discussion (Misc queries) | |||
Lock Cell After Data Entry | Excel Discussion (Misc queries) |