Protect After Saved
 

Dear experts,

I've a worksheet where a range A5:D100 is for some users to fill in
information. Is there a way when the cell is blank, it can be input by any
users. However, whenver an user fill in some information to some rows within
the range, the non-blank cells will be protected by a password, say "tiptoe"
after saved but other blank cells will still be unprotected and can be input
by other users. Is there a VBA code can do this? If yes, please kindly advise
the code.

Thanks in advance.

Protect After Saved
 

Select A5:D100 and set properties to "unlocked". All other cells set to
"locked"

Right-click on the sheet tab and "View Code". Copy/paste this into the module
that opens up.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A5:D100")) Is Nothing Then
ActiveSheet.Unprotect Password:="tiptoe"
If Target.Value < "" Then
Target.Locked = True
End If
End If
enditall:
Application.EnableEvents = True
ActiveSheet.Protect Password:="tiptoe"
End Sub

Alt + q to return to the Excel sheet.

As you enter data in A5:D100 the cells will become locked.


Gord Dibben MS Excel MVP

On Thu, 19 Jun 2008 23:06:01 -0700, Freshman
wrote:

Dear experts,

I've a worksheet where a range A5:D100 is for some users to fill in
information. Is there a way when the cell is blank, it can be input by any
users. However, whenver an user fill in some information to some rows within
the range, the non-blank cells will be protected by a password, say "tiptoe"
after saved but other blank cells will still be unprotected and can be input
by other users. Is there a VBA code can do this? If yes, please kindly advise
the code.

Thanks in advance.

Protect After Saved
 

Dear Gord,

Thanks for your time to write me the code. I'm really appreciated it. Have a
nice weekend.

Best regard to you and your family.

"Gord Dibben" wrote:

Select A5:D100 and set properties to "unlocked". All other cells set to
"locked"

Right-click on the sheet tab and "View Code". Copy/paste this into the module
that opens up.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A5:D100")) Is Nothing Then
ActiveSheet.Unprotect Password:="tiptoe"
If Target.Value < "" Then
Target.Locked = True
End If
End If
enditall:
Application.EnableEvents = True
ActiveSheet.Protect Password:="tiptoe"
End Sub

Alt + q to return to the Excel sheet.

As you enter data in A5:D100 the cells will become locked.


Gord Dibben MS Excel MVP

On Thu, 19 Jun 2008 23:06:01 -0700, Freshman
wrote:

Dear experts,

I've a worksheet where a range A5:D100 is for some users to fill in
information. Is there a way when the cell is blank, it can be input by any
users. However, whenver an user fill in some information to some rows within
the range, the non-blank cells will be protected by a password, say "tiptoe"
after saved but other blank cells will still be unprotected and can be input
by other users. Is there a VBA code can do this? If yes, please kindly advise
the code.

Thanks in advance.

Protect After Saved
 

Thanks for the feedback and we will have a nice weekend.


Gord

On Sat, 21 Jun 2008 06:05:00 -0700, Freshman
wrote:

Dear Gord,

Thanks for your time to write me the code. I'm really appreciated it. Have a
nice weekend.

Best regard to you and your family.

"Gord Dibben" wrote:

Select A5:D100 and set properties to "unlocked". All other cells set to
"locked"

Right-click on the sheet tab and "View Code". Copy/paste this into the module
that opens up.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A5:D100")) Is Nothing Then
ActiveSheet.Unprotect Password:="tiptoe"
If Target.Value < "" Then
Target.Locked = True
End If
End If
enditall:
Application.EnableEvents = True
ActiveSheet.Protect Password:="tiptoe"
End Sub

Alt + q to return to the Excel sheet.

As you enter data in A5:D100 the cells will become locked.


Gord Dibben MS Excel MVP

On Thu, 19 Jun 2008 23:06:01 -0700, Freshman
wrote:

Dear experts,

I've a worksheet where a range A5:D100 is for some users to fill in
information. Is there a way when the cell is blank, it can be input by any
users. However, whenver an user fill in some information to some rows within
the range, the non-blank cells will be protected by a password, say "tiptoe"
after saved but other blank cells will still be unprotected and can be input
by other users. Is there a VBA code can do this? If yes, please kindly advise
the code.

Thanks in advance.