Protect Cell if value <> 0 OR blank?
 

Is it possible to protect a cell if the value is not equal to zero or blank?

I am using Excel 2002 and want to allow people to be able to enter values
into the worksheet, but once they have been entered, I do not want them to be
changed.

Thanks,

How about something like this (you need to copy this into the Workbook
module).
Regards,
KL

'--------------Code Start--------------
Const Password = "" '<=Your password here if any

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
'Prevent multicell selections.
If Target.Count 1 Then ActiveCell.Select
ActiveSheet.Unprotect Password
If IsEmpty(ActiveCell) Then
ActiveCell.Locked = False
Else
ActiveCell.Locked = True
End If
ActiveSheet.Protect Password
End Sub

'--------------Code Finish--------------

"newbie" wrote in message
...
Is it possible to protect a cell if the value is not equal to zero or
blank?

I am using Excel 2002 and want to allow people to be able to enter values
into the worksheet, but once they have been entered, I do not want them to
be
changed.

Thanks,

Newbie, here is one way, right click on the worksheet tab and view code,
paste this in the window that opens. Unlock all cells in the range first,
change the range to your range and the password to a password you want. Will
protect the cell after someone puts data in it



Private Sub Worksheet_Change(ByVal Target As Range)

Dim MyRange As Range



'change range here

Set MyRange = Intersect(Range("A1:B10"), Target)

If Not MyRange Is Nothing Then



'change password here

Unprotect password:="123"

MyRange.Locked = True

'change password here

Protect password:="123"

End If

End Sub
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003


"newbie" wrote in message
...
Is it possible to protect a cell if the value is not equal to zero or
blank?

I am using Excel 2002 and want to allow people to be able to enter values
into the worksheet, but once they have been entered, I do not want them to
be
changed.

Thanks,

Paul B,

This is what I was looking for, but this seems to protect the whole range,
ie; as soon as there is data in one cell, the whole range is protected.

Is there any way that I can protect each cell individually, so that I can
still enter values into the blank cells?

Thanks,


"Paul B" wrote:

Newbie, here is one way, right click on the worksheet tab and view code,
paste this in the window that opens. Unlock all cells in the range first,
change the range to your range and the password to a password you want. Will
protect the cell after someone puts data in it



Private Sub Worksheet_Change(ByVal Target As Range)

Dim MyRange As Range



'change range here

Set MyRange = Intersect(Range("A1:B10"), Target)

If Not MyRange Is Nothing Then



'change password here

Unprotect password:="123"

MyRange.Locked = True

'change password here

Protect password:="123"

End If

End Sub
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003


"newbie" wrote in message
...
Is it possible to protect a cell if the value is not equal to zero or
blank?

I am using Excel 2002 and want to allow people to be able to enter values
into the worksheet, but once they have been entered, I do not want them to
be
changed.

Thanks,

KL...

Sorry, but what is the "workbook module"?

Thanks,

"KL" wrote:

How about something like this (you need to copy this into the Workbook
module).
Regards,
KL

'--------------Code Start--------------
Const Password = "" '<=Your password here if any

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
'Prevent multicell selections.
If Target.Count 1 Then ActiveCell.Select
ActiveSheet.Unprotect Password
If IsEmpty(ActiveCell) Then
ActiveCell.Locked = False
Else
ActiveCell.Locked = True
End If
ActiveSheet.Protect Password
End Sub

'--------------Code Finish--------------

"newbie" wrote in message
...
Is it possible to protect a cell if the value is not equal to zero or
blank?

I am using Excel 2002 and want to allow people to be able to enter values
into the worksheet, but once they have been entered, I do not want them to
be
changed.

Thanks,

Right-click on any sheet's name tab at the bottom of the window and select
View Code - the VBE window will appear. Double-click on ThisWorkbook object
in the Project Explorer - the workbook's module will open in the right
window. Copy the code there.

Regards,
KL

"newbie" wrote in message
...
KL...

Sorry, but what is the "workbook module"?

Thanks,

"KL" wrote:

How about something like this (you need to copy this into the Workbook
module).
Regards,
KL

'--------------Code Start--------------
Const Password = "" '<=Your password here if any

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
'Prevent multicell selections.
If Target.Count 1 Then ActiveCell.Select
ActiveSheet.Unprotect Password
If IsEmpty(ActiveCell) Then
ActiveCell.Locked = False
Else
ActiveCell.Locked = True
End If
ActiveSheet.Protect Password
End Sub

'--------------Code Finish--------------

"newbie" wrote in message
...
Is it possible to protect a cell if the value is not equal to zero or
blank?

I am using Excel 2002 and want to allow people to be able to enter
values
into the worksheet, but once they have been entered, I do not want them
to
be
changed.

Thanks,

KL,

Thanks for the clarification.

I must still be doing something wrong though as I kepp getting the following
error;

"Compile Error: Member already exists in an object module from which this
object module derives".

And it has highlighted "Password =" on the first line of the script.

Thanks,

"KL" wrote:

Right-click on any sheet's name tab at the bottom of the window and select
View Code - the VBE window will appear. Double-click on ThisWorkbook object
in the Project Explorer - the workbook's module will open in the right
window. Copy the code there.

Regards,
KL

"newbie" wrote in message
...
KL...

Sorry, but what is the "workbook module"?

Thanks,

"KL" wrote:

How about something like this (you need to copy this into the Workbook
module).
Regards,
KL

'--------------Code Start--------------
Const Password = "" '<=Your password here if any

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
'Prevent multicell selections.
If Target.Count 1 Then ActiveCell.Select
ActiveSheet.Unprotect Password
If IsEmpty(ActiveCell) Then
ActiveCell.Locked = False
Else
ActiveCell.Locked = True
End If
ActiveSheet.Protect Password
End Sub

'--------------Code Finish--------------

"newbie" wrote in message
...
Is it possible to protect a cell if the value is not equal to zero or
blank?

I am using Excel 2002 and want to allow people to be able to enter
values
into the worksheet, but once they have been entered, I do not want them
to
be
changed.

Thanks,

You may already have the constant or variable "password" declared somewhere
in your project. You can just delete the first line (Const Password = ""
'<=Your password here if any) and delete the word Password or replace it
with your actual password between quotation marks in the following lines:

ActiveSheet.Unprotect Password

ActiveSheet.Protect Password

KL

"newbie" wrote in message
...
KL,

Thanks for the clarification.

I must still be doing something wrong though as I kepp getting the
following
error;

"Compile Error: Member already exists in an object module from which this
object module derives".

And it has highlighted "Password =" on the first line of the script.

Thanks,

"KL" wrote:

Right-click on any sheet's name tab at the bottom of the window and
select
View Code - the VBE window will appear. Double-click on ThisWorkbook
object
in the Project Explorer - the workbook's module will open in the right
window. Copy the code there.

Regards,
KL

"newbie" wrote in message
...
KL...

Sorry, but what is the "workbook module"?

Thanks,

"KL" wrote:

How about something like this (you need to copy this into the Workbook
module).
Regards,
KL

'--------------Code Start--------------
Const Password = "" '<=Your password here if any

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
'Prevent multicell selections.
If Target.Count 1 Then ActiveCell.Select
ActiveSheet.Unprotect Password
If IsEmpty(ActiveCell) Then
ActiveCell.Locked = False
Else
ActiveCell.Locked = True
End If
ActiveSheet.Protect Password
End Sub

'--------------Code Finish--------------

"newbie" wrote in message
...
Is it possible to protect a cell if the value is not equal to zero
or
blank?

I am using Excel 2002 and want to allow people to be able to enter
values
into the worksheet, but once they have been entered, I do not want
them
to
be
changed.

Thanks,

KL,

Thank you. I have now got exactly what I was looking for.

:-)

"KL" wrote:

You may already have the constant or variable "password" declared somewhere
in your project. You can just delete the first line (Const Password = ""
'<=Your password here if any) and delete the word Password or replace it
with your actual password between quotation marks in the following lines:

ActiveSheet.Unprotect Password

ActiveSheet.Protect Password

KL

"newbie" wrote in message
...
KL,

Thanks for the clarification.

I must still be doing something wrong though as I kepp getting the
following
error;

"Compile Error: Member already exists in an object module from which this
object module derives".

And it has highlighted "Password =" on the first line of the script.

Thanks,

"KL" wrote:

Right-click on any sheet's name tab at the bottom of the window and
select
View Code - the VBE window will appear. Double-click on ThisWorkbook
object
in the Project Explorer - the workbook's module will open in the right
window. Copy the code there.

Regards,
KL

"newbie" wrote in message
...
KL...

Sorry, but what is the "workbook module"?

Thanks,

"KL" wrote:

How about something like this (you need to copy this into the Workbook
module).
Regards,
KL

'--------------Code Start--------------
Const Password = "" '<=Your password here if any

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
'Prevent multicell selections.
If Target.Count 1 Then ActiveCell.Select
ActiveSheet.Unprotect Password
If IsEmpty(ActiveCell) Then
ActiveCell.Locked = False
Else
ActiveCell.Locked = True
End If
ActiveSheet.Protect Password
End Sub

'--------------Code Finish--------------

"newbie" wrote in message
...
Is it possible to protect a cell if the value is not equal to zero
or
blank?

I am using Excel 2002 and want to allow people to be able to enter
values
into the worksheet, but once they have been entered, I do not want
them
to
be
changed.

Thanks,