Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COPY A CONCATENATE CELL TO BLANK CELL PUTTING IN THE NEXT BLANK C. | Excel Discussion (Misc queries) | |||
conditional formatting blank cell | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions | |||
Make a cell blank? | Excel Worksheet Functions | |||
VLookup resulting in a blank cell... | Excel Worksheet Functions |