• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Lock (data entry restriction) on range of cells based on value on adjacent cell.

Sanoj

Sanoj

Member
Hello,

I was trying to lock range of cell say A:HR if value of HR for that particular row is "Approved".

There are two sheets
1. View_Form - to view specific selected values and click macro button Approve.
2. Tracker - Data base where Cell HR updates while clicking Approval button of sheet 1

Wanted to lock range of cell A:HR if value of HR cell for that row is "Approved". sheet Tracker is password protected as "123". So the VBA should first unlock the sheet Tracker and after running the code should lock the cell back with password "123".

Any help??? this is the current code I am using for approval.
Code: 
Sub Approval()
Dim found As Range 'define variables
Dim SelectedFileID As String

ActiveSheet.Unprotect "123" '---------------Unprotect sheet

'Approval function
SelectedFileID = Sheets("View_Form").Range("SelFileID").Value 'get the currently selected File ID

Set found = Sheets("Tracker").Range("B:B").Find(What:=SelectedFileID) 'find the file ID in the Sheet Tracker
    If Not found Is Nothing Then 'if found
        Sheets("Tracker").Cells(found.Row, 226).Value = "Approved" 'change the value of the row it was found, but column 226 which is column HR
    Else
        MsgBox "ID not found in Sheet Tracker!", vbInformation 'if not found then show message
    End If
ActiveSheet.Protect "123"

    ActiveWorkbook.Save '---------------Save workbook
    Application.DisplayAlerts = False
End Sub
 
vletm said:
https://chandoo.org/forum/threads/v...ected-row-and-lock-the-row.36712/#post-220314
? hmm ... Why new case from same case? ... hmm?
I used the below code its working just as I need.
Another forum helped me out very quickly.
Yesterday You wrote that it works as You need, what happened?
I already made a sample which should work as You want.
Click to expand...

That was just for approval, Now to improve it more trying to lock the cell. your response was not to the point even after clearing your queries there were still confusions. This thread is completely different to the thread you mentioned above.
 
Last edited:
It's same case as
LukeM also wrote about Cross-Posting and
You commented:
"Yes, sure going a head will see to the points you raised and will keep posted."

I asked: Are those sheets Your final versions?
If Your clearing was: 'Hi, Please find the attached final file.'

I had ALSO asked 'Did You test it?' No answer!

My sample 'locks' row after 'Approved' and
You cannot edit that row after that (normal way).
My sample let You 'lock-by-cell&row' as You wished;
normally locking do it with the whole sheet!
 
vletm said:
It's same case as
LukeM also wrote about Cross-Posting and
You commented:
"Yes, sure going a head will see to the points you raised and will keep posted."
I asked: Are those sheets Your final versions?
If Your clearing was: 'Hi, Please find the attached final file.'

I had ALSO asked 'Did You test it?' No answer!

My sample 'locks' row after 'Approved' and
You cannot edit that row after that (normal way).
My sample let You 'lock-by-cell&row' as You wished;
normally locking do it with the whole sheet!
Click to expand...

1. "Yes, sure going a head will see to the points you raised and will keep posted." - It means will inform in future if already its resolved.
2. If Your clearing was: 'Hi, Please find the attached final file.' - I have provided you final file.
3. I had ALSO asked 'Did You test it?' No answer! - Because I mentioned very clearly in my question that "Wanted to lock range of cell A:HR if value of HR cell for that row is "Approved".
but you asked more questions with case 1 case 2 which was creating more confusion, where as my question was very clear.

I request that, we could discuss this in personal posting instead of posting in this thread as its deviating the topic.
 
You must log in or register to reply here.
Back
Top