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

Bugs
If all you want to do is prevent the user from altering the contents of
a cell that already has an entry, and allow an entry if the cell was blank,
I suggest the following macro and forget about locking/protecting cells and
sheets. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewValue As Variant, OldValue As Variant
If Target.Count 1 Then Exit Sub
'Say you want to work with the range A1:F100
If Not Intersect(Target, Range("A1:F100")) Is Nothing Then
NewValue = Target.Value
Application.EnableEvents = False
Application.Undo
OldValue = Target.Value
If OldValue < "" Then
MsgBox "You cannot alter the contents of this cell.", 16,
"Invalid"
Target.Value = OldValue
Else
Target.Value = NewValue
End If
Application.EnableEvents = True
End If
End Sub

"Bugs" <celebimehmet«no wrote in message
...
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

Hi Mr. Otto, you're right.
I have an ftp site. I want to create an excel sheet on it for tracking file
uploads.
Users will open the file and then they will be add a record like this :
"i uploaded the xyx.txt file on 01.05.2009 - Bugs"

When they make this changes i want to protect old records..

I tried your macro but nothings happen :(
May be i making an error when i try to use it.
Could you please explain me how can i use it ?

I think now there was another big problem if i use vba to do this. If users
goes to vba editor menu, they can be found the protection password in the
vba code :)

Thanks for help & best regards..
Bugs


"Otto Moehrbach" wrote in message
...
Bugs
If all you want to do is prevent the user from altering the contents of
a cell that already has an entry, and allow an entry if the cell was
blank, I suggest the following macro and forget about locking/protecting
cells and sheets. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewValue As Variant, OldValue As Variant
If Target.Count 1 Then Exit Sub
'Say you want to work with the range A1:F100
If Not Intersect(Target, Range("A1:F100")) Is Nothing Then
NewValue = Target.Value
Application.EnableEvents = False
Application.Undo
OldValue = Target.Value
If OldValue < "" Then
MsgBox "You cannot alter the contents of this cell.", 16,
"Invalid"
Target.Value = OldValue
Else
Target.Value = NewValue
End If
Application.EnableEvents = True
End If
End Sub

"Bugs" <celebimehmet«no wrote in message
...
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.

Bugs
This macro is a sheet event macro and must be placed in the sheet module
of the pertinent sheet. As written, this macro will do what you want but
only if the changed cell is in the range A1:F100. I chose that range
arbitrarily. Change that in the macro as needed. To access that module,
right-click on the sheet tab and select View Code. Paste this macro in that
module. "X" out of the module to return to your sheet. There are no
passwords used in this macro. Be aware that the user must open the file
with macros enabled. HTH Otto
"Bugs" <celebimehmet«no wrote in message
...
Hi Mr. Otto, you're right.
I have an ftp site. I want to create an excel sheet on it for tracking
file uploads.
Users will open the file and then they will be add a record like this :
"i uploaded the xyx.txt file on 01.05.2009 - Bugs"

When they make this changes i want to protect old records..

I tried your macro but nothings happen :(
May be i making an error when i try to use it.
Could you please explain me how can i use it ?

I think now there was another big problem if i use vba to do this. If
users goes to vba editor menu, they can be found the protection password
in the vba code :)

Thanks for help & best regards..
Bugs


"Otto Moehrbach" wrote in message
...
Bugs
If all you want to do is prevent the user from altering the contents
of a cell that already has an entry, and allow an entry if the cell was
blank, I suggest the following macro and forget about locking/protecting
cells and sheets. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewValue As Variant, OldValue As Variant
If Target.Count 1 Then Exit Sub
'Say you want to work with the range A1:F100
If Not Intersect(Target, Range("A1:F100")) Is Nothing Then
NewValue = Target.Value
Application.EnableEvents = False
Application.Undo
OldValue = Target.Value
If OldValue < "" Then
MsgBox "You cannot alter the contents of this cell.", 16,
"Invalid"
Target.Value = OldValue
Else
Target.Value = NewValue
End If
Application.EnableEvents = True
End If
End Sub

"Bugs" <celebimehmet«no wrote in message
...
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.

Otto,

Your code indeed works for a cell has focus however, the cell contents
can be overwritten when I select a range which includes this cell, clear
the contents then insert new data.


--
greman
------------------------------------------------------------------------
greman's Profile: http://www.thecodecage.com/forumz/member.php?userid=498
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=95482