How to protect a cell INSTANTLY the moment the data is entered!!

  • Thread starter Thread starter Moti
  • Start date Start date
M

Moti

Hello,
I want to know how to protect the cell at the very moment data i
keyed in and entered without using the protection option in the tool'
bar! I would like the process to be automatic!! for example: in cell B
if i type in the word "king" and press ENTER! after this i must not b
able to modify the word as the cell should be automatically protected
if any one has a response to this please reply i would be eagerl
waiting for it!

thanx!
Mot
 
You could do this by having the cell unprotected>entering the data>have a
worksheet_change event unprotect the worksheet >lock that cell> re-protect
the worksheet. Try recording it yourself to see how it would work.
 
Moti, try this, change the range, password to what you want and unlock the
cells in the range first. you will also need to protect the VBA project, so
people cannot see the password.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
Set MyRange = Intersect(Range("A1:D100"), Target)
If Not MyRange Is Nothing Then
Unprotect password:="123"
MyRange.Locked = True
Protect password:="123"
End If
End Sub


To put in this macro right click on the worksheet tab and view code, in the
window that opens paste this code, press Alt and Q to close this window and
go back to your workbook. If you are using excel 2000 or newer you may have
to change the macro security settings to get the macro to run.

To protect the VBA project, from your workbook right-click the workbook's
icon and pick View Code. This icon is to the left of the "File" menu this
will open the VBA editor, in the left hand window right click on your
workbook name and select VBA project properties, protection, check lock
project for viewing and set a password. Press Alt and Q to close this window
and go back to your workbook and save and close the file. Be aware that this
password can be broken by third party software
--
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 2000 & 97
** remove news from my email address to reply by email **
 
Moti

u should use validation from Data menu.

in Error Alert> Style - give Stop.

Data will protect.

s kara
 
Dear Don Guillett,
Thank you very much for your reply. But m
problem is that my version of excel 2000 is in spanish therefore ia
not able to understand it so well. Do you know how can i get
translation or how could i change the language of excel. I don't kno
in which menu i could get the "worksheet_change event unprotect th
worksheet" as my version is in spanish! please help me out!

thank you
rgds
Moti.
e-mail: (e-mail address removed)
 
Dear Paul B,
Thanx for your formula. It works well but, i want t
protect only the cell in which the data is entered the whole worksheet
please help me out.

Thank you
Rgds
Moti
e-mail: (e-mail address removed)
 
hi, Moti >!
... i want to protect only the cell in which the data is entered not the whole worksheet.
Click to expand...

1.- 'right-click' on your worksheet-tab and select 'view code' [ver codigo if spanish]
2.- within the code-window, copy/paste the following lines...
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not IsEmpty(Target) Then Target.Offset(, 1).Select
End Sub

pros: end-user will not be able to select 'pre-entered' cells
cons: end-user will not be able to select 'pre-entered' cells

HTH,
hector.
 
Hi Héctor Miguel,
Your formula worked greatly, but it does no
give me any option to modify the information on the cell. To modify th
cell i have to enter in VBA project and write ('0) instead of ('1) ca
it be possible to disprotect each cell with password and modify it thi
way!?
 
hi, Moti > !
Your formula worked greatly, but it does not give me any option to modify the information on the cell [...]
Click to expand...
_____
first... i thank you for the 'feed-back' ;)
second... if you remember my 'advise'... :(
- pros: end-user will not be able to select 'pre-entered' cells
- cons: end-user will not be able to select 'pre-entered' cells
¨¨¨¨¨¨¨¨
... can it be possible to disprotect each cell with password and modify it this way!?.
Click to expand...
____
third... you could use [some] tricks-combination [i'm suggesting two 'forms']

HTH,
hector.

___ 1 ___
=== in your worksheet-code-module ===
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If IsEmpty(Target) Then Exit Sub
If InputBox("... and, your authorization is...?", "") <> "Hocus Pocus" _
Then Range("iv" & Target.Row).End(xlToLeft).Offset(, 1).Select
End Sub
=> change 'Hocus Pocus' for a password of your convinience ;)
=> if you don't want to 'see' the characters [inputbox]... a userform is needed
=> if an empty cell is 'filled' and '{enter}' pressed... the '_selectionchange' event is triggered again
[given that 'move selection after enter' is NOT used] => tools / options / [tab] modify -> move selection...

___ 2 ___ [previous] ___
other situations you [may] 'have to' handle...
the 'navigation' keys/features that interfere to detect =>IF<= a '_selectionchange' event has occured...
1.- 'move selection after enter' [and which direction it moves]
2.- a 'multi-cells' range selection [while 'navigating' within... '_selectionchange' event DOESN'T triggers]
[maybe] 'non-empty' cell/s could be part of them, and... '_selectionchange' event passes it by
i'm suggesting 'avoid' the user selects more than one cell [and 'trap' {enter} and {return} keys]
if multi-cells range/s is/are to be allowed... you have to 'trap' [a lot of] more 'navigation-keys'
___ 2 ___
Code: 
 ___
=== in your worksheet-code-module ===
Dim EnterMove As Boolean
Private Sub Worksheet_Activate()
With Application
EnterMove = .MoveAfterReturn
.MoveAfterReturn = False
.OnKey "{Enter}", "No_Tracking"
.OnKey "~", "No_Tracking"
End With
End Sub
Private Sub Worksheet_Deactivate()
With Application
.MoveAfterReturn = EnterMove
.OnKey "{Enter}": .OnKey "~"
End With
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then
Application.EnableEvents = False
ActiveCell.Select
Application.EnableEvents = True
End If
If IsEmpty(ActiveCell) Then Exit Sub
If InputBox("... and, your authorization is...?", "") _
<> "Hocus Pocus" Then
MsgBox "WRONG PassWord... <\°|°/>", vbCritical, ""
Cells(65536, ActiveCell.Column).End(xlUp).Offset(1).Select
End If
End Sub
=== in a 'normal' code module ===
Option Private Module
Sub No_Tracking()
Application.EnableEvents = False
Application.OnTime Now + TimeValue("0:00:01"), "Restore_Events"
End Sub
Sub Restore_Events()
Application.EnableEvents = True
End Sub
 
Dear Héctor Miguel,
Sorry to reply u so late. Your Formul
really worked well, iam really very pleased. I now have another proble
for you to solve.
I would like to be advised and given some specific formula for th
following problem. I have created a database with some lottery draw
from specific dates of this year. Unfortuately I am enable to work ou
the formula needed to count the number of delays from the last draw
For example: in the database attached the numbers extracted from th
first draw are written from the cells B3 to G3 and downward
collectively as the number of draws increases. From cells I3 to N3 an
downwards the number of delays from the last draw are represente
manually until I17 to N17. I would appreciate a given formula to wor
out the number of delays automatically with the formula to save up som
time!

I will be waiting for your response desperately.
thanking you
MOTI
 
hi, Moti > !
... Your Formula really worked well ... very pleased.
Click to expand...
¨¨¨¨¨¨¨¨¨¨¨¨¨¨
i thank you again for the 'feed-back' [it's nice to know when a proposal works] ;)
... another problem ... to solve ... a database with some lottery draws
... the formula needed to count the number of delays from the last draw.
... the numbers extracted from the first draw are written from the cells B3 to G3 and downwards [...]
... cells I3 to N3 and downwards the number of delays from the last draw are represented manually [...]
... formula to work out the number of delays automatically with the formula to save up some time! [...]
Click to expand...
¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨
i'm not 'familiar' with lottery [analysis, stats, tracking, ...] :((
but, it 'sounds' [to me] that you want an 'automatic-track-count' for each number since their last [draw] occurrence
if that is correct [and given that first draw -'B3:G3'- does not need to be 'tracked']...
try the following formula in cell 'I4' =>array formula... entered with ctrl+shift+enter, rather than 'just' enter<=
=If(Max((b$3:b3=b4)*(Row(b$3:b3)))>0,Row()-Max((b$3:b3=b4)*(Row(b$3:b3))),"")
and... copy/paste to range 'J4:N4'
select entire range ['I4:N4'] and... copy downward as needed ['I5:I17' ???]

again... if application's language is spanish... [starting] formula should be...
=Si(Max((b$3:b3=b4)*(Fila(b$3:b3)))>0,Fila()-Max((b$3:b3=b4)*(Fila(b$3:b3))),"")

hth,
hector.
 
Dear héctor Miguel

Heartly Thanks for your help,you follow me very wel
that i want to track automatically delay of occur nº from last draw
lotto has 49nº to win needs only combination of six numbers it is no
so simple as looks,formula you suggest works well to know nº ha
extract in this draw but what i want to know each time extract n
should be reset and start counting as new delay for next draw, here i
example file attach zip xls format


I will be waiting for your responce desperately
thanking you
MOT

Attachment filename: count delay.zip
Download attachment: http://www.excelforum.com/attachment.php?postid=59204
 
Dear Héctor Miguel

You are geniuses, received file i do not believe but Formula works as
i wanted, heartly thanks for this great work, I was working on this
formula since last three monts but today i am feeling relaxed send mi
your e-mail

here is my e-mail (e-mail address removed)

Your Thankfull
MOTI
 
Back
Top