AUTO PROTECT CELLS AFTER ENTERING DATA ONCE SAVED

[SARJIL VHORA]

Hi,

I want to protect few cells such away that users can not edit data in few cells once file saved. i have hereby attached my excel sheet, i want to apply above protection in yellow highlighted raw.
please help me.. is it possible ??

 

[Sathish KV]

Hi,

You have highlighted two columns

1) Do you wan to protect whole column or only the cells which are not blank

2) This can be done only through macro. Would it be ok?

 

[SARJIL VHORA]

I have to protect whole column.... and it will be OK with macro....

 

[Sathish KV]

Hi,

Try the below code in workbook module

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 
Sheet1.Unprotect Password:="password"
 
Lr = Rows.Count
Range("G3:G" & Lr).Locked = True
Range("K3:K" & Lr).Locked = True
   
Sheet1.Protect Password:="password"
 
 
   
End Sub

 

[SARJIL VHORA]

it is not working dear..

 

[Sathish KV]

Where did you place the code

 

[SARJIL VHORA]

In view code module..

 

[Sathish KV]

In the VBA screen you will have option as Workbook. Double click on it and paste the code there

 

[SARJIL VHORA]

if you dont mind.. could you please explain in brief ??? how to add code ???

 

[Sathish KV]

Go to VBA screen and left side of the screen you can see 'Workbook'

Double click on it and on the right side paste the above code

 

[SARJIL VHORA]

I have done same thing but following problem occurs:
- At present user can not enter value even in new cells also

 

[Sathish KV]

Hi,

The whole sheet is password protected

You need to unlock the cells which ever you need to be open even after sheet is protected with password

To do this select all the required cells to be unlocked, righclik and go to format,

In the format pop-up goto Protection Tab, Under protection tab uncheck the option 'Locked'

 

[SARJIL VHORA]

Hi,
still my problem is not solved... what i want ???
- if user enters data in yellow highlighted cells and saves files then he can not allowed to re-enter data in old cells but he can enter data in new cells.

 

[Sathish KV]

I have to protect whole column.... and it will be OK with macro....

Hi,

Previously I have checked with you if you want to protect whole column or the cells which are not blank and you said 'whole column'

 

[Sathish KV]

Hi,

Find the below code

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.ScreenUpdating = False
 
Sheet1.Unprotect Password:="password"
Lr = Rows.Count
 
Range("G3:G" & Lr).Select
Selection.SpecialCells(xlCellTypeConstants, 23).Select
Selection.Locked = True
 
Range("G3:G" & Lr).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Locked = False
 
 
Range("K3:K" & Lr).Select
Selection.SpecialCells(xlCellTypeConstants, 23).Select
Selection.Locked = True
 
Range("K3:K" & Lr).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Locked = False
 
Sheet1.Protect Password:="password"
 
Application.ScreenUpdating = True
 
End Sub

 

[SARJIL VHORA]

Thanks a lot but when i try to save i found error as per below:

 

[Sathish KV]

Try below code

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 
on error exit sub
Application.ScreenUpdating = False
Sheet1.Unprotect Password:="password"
Lr = Rows.Count
Range("G3:G" & Lr).Select
Selection.SpecialCells(xlCellTypeConstants, 23).Select
Selection.Locked = True
Range("G3:G" & Lr).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Locked = False
Range("K3:K" & Lr).Select
Selection.SpecialCells(xlCellTypeConstants, 23).Select
Selection.Locked = True
Range("K3:K" & Lr).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Locked = False
Sheet1.Protect Password:="password"
Application.ScreenUpdating = True
End Sub

 

[SARJIL VHORA]

below error observed

 

[Sathish KV]

This will work

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
On Error Goto Err
Application.ScreenUpdating = False
Sheet1.Unprotect Password:="password"
Lr = Rows.Count
Range("G3:G" & Lr).Select
Selection.SpecialCells(xlCellTypeConstants, 23).Select
Selection.Locked = True
Range("G3:G" & Lr).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Locked = False
Range("K3:K" & Lr).Select
Selection.SpecialCells(xlCellTypeConstants, 23).Select
Selection.Locked = True
Range("K3:K" & Lr).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Locked = False
Sheet1.Protect Password:="password"
Application.ScreenUpdating = True
 
Err:
Exit Sub
End Sub

 

[SARJIL VHORA]

Error during saving is solved but not fulfill prime requirement.. still user can edit previous data in yellow highlighted cells

 

[Sathish KV]

Fill Cell G3 and K3 with some data and save. now it should be working

 

[SARJIL VHORA]

thank you very much.... its working perfectly now.....

 

[SARJIL VHORA]

Hi,
one problem I am facing while using this sheet is user can not use option such as autofilter etc.....
is it provision to enable these ?? i have tried to enable by marking tick on protect sheet tab but it works untill i save sheets. afterwards user can not use..

 

[SARJIL VHORA]

Hi,
please help...

 

[Sathish KV]

Hi,

Use the below code

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
On Error Goto Err
Application.ScreenUpdating = False
Sheet1.Unprotect Password:="password"
Lr = Rows.Count
Range("G3:G" & Lr).Select
Selection.SpecialCells(xlCellTypeConstants, 23).Select
Selection.Locked = True
Range("G3:G" & Lr).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Locked = False
Range("K3:K" & Lr).Select
Selection.SpecialCells(xlCellTypeConstants, 23).Select
Selection.Locked = True
Range("K3:K" & Lr).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Locked = False
Sheet1.Protect Password:="password", AllowFiltering:=True
Application.ScreenUpdating = True
Err:
Exit Sub
End Sub