Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 9th 04, 02:15 PM
newbie
 
Posts: n/a
Default 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,

  #2   Report Post  
Old December 9th 04, 03:58 PM
KL
 
Posts: n/a
Default

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,



  #3   Report Post  
Old December 9th 04, 04:01 PM
Paul B
 
Posts: n/a
Default

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,



  #4   Report Post  
Old December 10th 04, 02:45 PM
newbie
 
Posts: n/a
Default

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,




  #5   Report Post  
Old December 10th 04, 02:49 PM
newbie
 
Posts: n/a
Default

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,






  #6   Report Post  
Old December 10th 04, 02:58 PM
KL
 
Posts: n/a
Default

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,






  #7   Report Post  
Old December 10th 04, 04:57 PM
newbie
 
Posts: n/a
Default

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,






  #8   Report Post  
Old December 10th 04, 06:38 PM
KL
 
Posts: n/a
Default

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,








  #9   Report Post  
Old December 13th 04, 10:23 AM
newbie
 
Posts: n/a
Default

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,











Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
COPY A CONCATENATE CELL TO BLANK CELL PUTTING IN THE NEXT BLANK C. QUEST41067 Excel Discussion (Misc queries) 1 January 15th 05 10:29 PM
conditional formatting blank cell TREK5200 Excel Discussion (Misc queries) 1 December 6th 04 03:23 AM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM
Make a cell blank? Matthew Leingang Excel Worksheet Functions 1 November 23rd 04 05:54 PM
VLookup resulting in a blank cell... KempensBoerke Excel Worksheet Functions 1 October 28th 04 09:57 PM


All times are GMT +1. The time now is 09:09 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright ©2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017