individual cell protection

B

bill gillan

I wish to allow people to enter data etc into a cell, but once entered, that
information can be deleted.

Any help would be greatly appreciated.

Thanks

Bill
 
P

Peo Sjoblom

Do you mean "but once entered information cannot be deleted"?

--
Regards,

Peo Sjoblom

(No private emails please)
 
B

bill gillan

Thanks for your response. Yes, what I want is to allow staff to enter data
into cells but not be able to then delete that information after they have
entered. Therefore have "input permission" but not deletion. I hope this
makes sense.

Regards

Bill
 
D

Dave Peterson

How do they fix a typo?

But if you want...

rightclick on the worksheet tab that should have this behavior. Select view
code and paste this in the code window.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myCell As Range
Me.Unprotect Password:="hi"
For Each myCell In Target.Cells
If myCell.Value = "" Then
'do nothing
Else
myCell.Locked = True
End If
Next myCell
Me.Protect Password:="hi"

End Sub

Change the password in both spots.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
B

bill gillan

Hi Dave

Thanks for your reply. It has not quiet worked how I wanted it to. If I
explain what I want then maybe it will help. I want to create a form that
staff will be required to input data into. There will be a range of cells
where they have to enter data. The columns will be C & D with rows 1 to 20.
So if they were to enter a figure in column C3 once entered they cannot
change it. I have pasted your code into the spreadsheet. It allowed me to
enter into a cell once but then not delete it. When I went to test this in
another cell I could not enter or delete. Any more advice/help would be
greatly appreciated. I do thank your for your time.

Regards

Bill
 
D

Dave Peterson

The first thing you have to do is lock the cells that can't be changed
(formulas/headers/instructions). And unlock all those cells (C1:d20) that can
be changed once.

Then give it a try.
 
J

Jason

This is great. Is there a way to protect only one set of cells while
allowing input and reinput into other sections of the spreadsheet?
 
D

Dave Peterson

You can lock/unlock any cell you want (format|cells|protection tab)

Then protect the worksheet (tools|protection|protect sheet)

Any unlocked cell can have its contents changed. And locked cell will be, er,
locked.

If you have lots of unprotected or lots of protected cells, you can select them
all (ctrl-a twice or three times), the change that setting to what you want.

Then come back and select the cells that are different and change that setting.

Then protect the worksheet.
This is great. Is there a way to protect only one set of cells while
allowing input and reinput into other sections of the spreadsheet?
Click to expand...
 
J

Jason

Thanks. To be more specific, I have a spreadsheet that has input into 7
different columns and I want only one column to be protected so that once the
data is entered, it cannot be changed. (I was successful at this based on
the directions below) However, I would like the other columns to be able to
be changed going forward. In the code you provided below, can there be a
range specified that the code would apply to only and not impact the rest of
the spreadsheet?
 
D

Dave Peterson

Lock/unlock all the cells you want. But leave some cells unlocked in your
column (where the user can make changes).

Then protect the worksheet.

Then this code may work the way you want:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRng As Range
Dim myCell As Range

Set myRng = Me.Range("c:C")

If Intersect(Target, myRng) Is Nothing Then
Exit Sub
End If

Me.Unprotect Password:="hi"
For Each myCell In Target.Cells
If myCell.Value = "" Then
'do nothing
Else
myCell.Locked = True
End If
Next myCell
Me.Protect Password:="hi"

End Sub

Thanks. To be more specific, I have a spreadsheet that has input into 7
different columns and I want only one column to be protected so that once the
data is entered, it cannot be changed. (I was successful at this based on
the directions below) However, I would like the other columns to be able to
be changed going forward. In the code you provided below, can there be a
range specified that the code would apply to only and not impact the rest of
the spreadsheet?
Click to expand...
 
N

Nick lee

This is great exactly what i needed also, their is one exception. How do I
get it to function with a shared workbook. I have it set up in my CNC
department for entering recut information. I have 4 computers that use the
same file on the network.
 
Top