VBA for Approval of selected row and lock the row.

[Sanoj]

Dear Coders,

Please help me out I am working on the attached file, and what exactly I was looking for is a vba code to set approval of data entered in the sheet "Tracker".

From the Sheet "Form (VIEW)" we could select data recorded in another sheet as "Tracker" by selecting drop down cell highlighted in yellow under "Select File ID".
On sheet "form view" if we click Approval button (added at bottom) on the bases of file ID selected from the drop down the "tracker" sheet should change the cell (HR that is the last cell of the record row ) value corresponding to the selected file in "Form View".

Once the Tracker sheet row is updated as Approved that particular row should get locked from editing say with password as 123.

I believe explained what I am looking for but in case any questions please let me know.

 

[vletm]

Sanoj
The 1st sample of 'Locking' row after press [Approved] (It's without password).
I didn't start to make 'change the cell (HR that is the last cell of the record row ) value corresponding to the selected file in "Form View".'
Are those sheets Your final versions?

 

[Sanoj]

Sanoj
The 1st sample of 'Locking' row after press [Approved] (It's without password).
I didn't start to make 'change the cell (HR that is the last cell of the record row ) value corresponding to the selected file in "Form View".'
Are those sheets Your final versions?

Hi Vletm, Thanks for your response, Yes as of now there is no password but what i am looking for is when we click Approve the respective row in Sheet "Tracker" should get locked with password say "1234", and at same time the HR cell should show value as "Approved" which is viewed and approved in "Form (VIEW)"
Hope, I answered your query is it possible

 

[vletm]

Sanoj
This version do not need to have password!
If really need to use password then the whole sheet would protect; not only row-by-row!
It 'locks' the respective row in Sheet 'Tracker' ...
did You test it?
... hmm ... of course macros have to accept ...
And as I wrote,
I didn't make that other part,
because I would like to know that are those sheets Your final versions?
(If even minor changes then that could mean extra coding)

 

[Sanoj]

Sanoj
This version do not need to have password!
If really need to use password then the whole sheet would protect; not only row-by-row!
It 'locks' the respective row in Sheet 'Tracker' ...
did You test it?
... hmm ... of course macros have to accept ...
And as I wrote,
I didn't make that other part,
because I would like to know that are those sheets Your final versions?
(If even minor changes then that could mean extra coding)

Hi Vletm, I have attached the final file thank you so much in advance.

Also, have one suggestion can we make entire sheet property as locked will get unlocked if HR cell value is "Approved". so that the sheet will be password protected but Unapproved rows can still be edited.

 

[Sanoj]

Hi Vletm, I have attached the final file thank you so much in advance.

Also, have one suggestion can we make entire sheet property as locked will get unlocked if HR cell value is "Approved". so that the sheet will be password protected but Unapproved rows can still be edited.

Hi, Please find the attached final file.

 

[vletm]

Sanoj
... as written in #4 Reply.
Did You test it?
Your one suggestion #5 Reply ... it works already?
It's challenge to modify if no reply!

 

[Sanoj]

Sanoj
... as written in #4 Reply.
Did You test it?
Your one suggestion #5 Reply ... it works already?
It's challenge to modify if no reply!

Thanks for your help @vletm
I used the below code its working just as I need.
Another forum helped me out very quickly.

Sub Approval()
Dim found As Range 'define variables
Dim SelectedFileID As String

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
End Sub

 

[Luke M]

Cross-posted here:
[url]https://stackoverflow.com/questions/47881471/approval-using-vba[/URL]

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread. This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Sanoj]

Cross-posted here:
https://stackoverflow.com/questions/47881471/approval-using-vba

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread. This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Yes, sure going a head will see to the points you raised and will keep posted.