Security in excel table/list database

[faizee]

Dear Excel Friends,
I am new on this board, this is my first post.
Can anyone help me, Is there any lock/security option in excel sheets, suppose we make some table/list and after entering one data when the cursor goes to second raw, the previous raw data automatically lock. the user cannot edit or delete that record. untill and unless authorise by admin, is it possible?
Thanks in advance!

 

[Sal Paradise]

Yes, it is possible using worksheet protection or data validation or worksheet protection and data validation, or vba code. How secure does it need to be?

 

[Smitty]

Welcome to the Board!

You can easily do that with a VB worksheet change event. Can you specify which column will receive the input that triggers the protection?

 

[faizee]

i tried worksheet protection by unchecked the "locked" in cell properties,then protech sheet. but after this i cannot add any new record. i want user to add any data in table/list but after adding data, user cannot delete or edit that data, he/she only could see this ..
and i dont have any idea abt data validation or vba for this issue. please help

 

[faizee]

the whole table should be lock,

 

[Smitty]

You need to specify which column you want to trigger the lock/protection. I.E. if a user enters data in column A, then the entire row should be locked.

 

[faizee]

USER NOT SUPPOSE TO ADD FOLLOWING WHOLE DATA, BUT USER CAN ADD NEW RECORD IN IT. FOR EXAMPLE, IF FIRST 6 RECORDS ARE ENTERED, THEN USER CANNNOT DELETE OR ADD IT. BUT HE/SHE ONLY CAN ADD NEW RECORD.
<TABLE style="WIDTH: 292pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=389 border=0><COLGROUP><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1865" width=51><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4315" width=118><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4096" width=112><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3949" width=108><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: #ece9d8; WIDTH: 38pt; COLOR: white; BORDER-BOTTOM: white 1.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=51 height=20>S#</TD><TD class=xl63 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: #ece9d8; WIDTH: 89pt; COLOR: white; BORDER-BOTTOM: white 1.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=118>NAME</TD><TD class=xl63 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: #ece9d8; WIDTH: 84pt; COLOR: white; BORDER-BOTTOM: white 1.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=112>F/NAME</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: #ece9d8; WIDTH: 81pt; COLOR: white; BORDER-BOTTOM: white 1.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=108>CONTACT NO.</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" height=20>1</TD><TD class=xl63 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none">JOHN</TD><TD class=xl63 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none">ZAID</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none">3333333</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" height=20>2</TD><TD class=xl63 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none">PAULA</TD><TD class=xl63 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none">S</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none">22222S</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" height=20>3</TD><TD class=xl63 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none">PETER</TD><TD class=xl63 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none">JR.</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none">EEEE</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" height=20>4</TD><TD class=xl63 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none">JOHN</TD><TD class=xl63 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none">ZAID</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none">3333333</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" height=20>5</TD><TD class=xl63 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none">PAULA</TD><TD class=xl63 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none">S</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #b8cce4; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none">22222S</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" height=20>6</TD><TD class=xl63 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none">PETER</TD><TD class=xl63 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none">JR.</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #ece9d8; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none">EEEE</TD></TR></TBODY></TABLE>
i am sorry. i think ,. i m not describing it properly.
please help!

 

[Sal Paradise]

Can't you just have the macro check the last row, and if it's completed, lock it, and if it's not complete, let the person finish it and unlock the next row?

 

[Smitty]

So it would appear that once data is entered in the Contact # column (column D), the row should be locked?

If so, then this may be a start:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("D:D")<br>             <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Action if Condition(s) are met</SPAN><br>            <SPAN style="color:#007F00">'   Do your thing here</SPAN><br>            ActiveSheet.Unprotect "PasswordGoesHere"<br>                Target.EntireRow.Locked = <SPAN style="color:#00007F">True</SPAN><br>            ActiveSheet.Protect "PasswordGoesHere"<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Note that you'll need to select the entire sheet and goto Format-->Cells-->Prtection-->Uncheck "Locked" first.

HTH,

 

[faizee]

sir,
i am stil confused about it, i want to data to become read-only, user cannot delete or edit it. only admin can do it.
can you please define it more clearly, i am new with VB in excel.