Protect a Cell - Only insert should be possible, not deletion

  • Thread starter Thread starter Fuzzy
  • Start date Start date
F

Fuzzy

Scenario...
I want to track the customers who walked in during a training.
5 key users will be using the shared workbook at the same time. Only one
column would be available for use. All others would be password protected.
The users will be able to select only "yes" from the drop down list.
Once selected and saved, no user should be able to delete this from the cell.

Please help.
 
Assumed Col A is used and rest of columns need to protect.

Ctrl + A | Ctrl +1 | protection tab | uncheck locked and hidden | ok

Select all the columns except Col A , Ctrl + 1 | protection tab |
check locked | ok

Now go tools | protection | protect sheet | ok

to share the workbook | tools | share workbook | ok
 
Thanks Muddan... but I dont think you understood.

I got how to protect all columns except the Col A...

my doubt is in the edit option for a cell in Col A.... user should only be
able to insert a word in the cell....user should not be able to delete the
same word once it has been inserted.....
 
Fuzzy

You need event code to lock the cell after a selection is made.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
Me.Protect Password:="justme", userinterfaceonly:=True
If Target.Value <> "" Then
Target.Locked = True
End If
End If
enditall:
Application.EnableEvents = True
End Sub

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

Edit password if you choose.

To prevent users from seeing the password and code, close that Module then.

Right-click on your workbook/project and select VBAProject
Properties>Protection>Lock from viewing.

Enter a password and save the workbook.


Gord Dibben MS Excel MVP
 
Back
Top