I am using vba code to review and Approve certain row of data using two set of sheets: 1st is "View_Form" where we review the entered data in specific form view. 2nd is "Tracker" where all the data is stored from external download.
In "View_Form" sheet we select File ID and all the data relevant to it get displayed and if all looks good we Click macro button "Approved" and the text "Approved" gets in the Column MY adjacent to the selected file ID else it would be blank.
It runs only once and then shows error as File ID not found. I want to restrict. That is if the MY cell contains text "Approved" that particular row from A:MY should get locked or should restrict the user from editing.
Should enable user to edit after using password to unprotect sheet say for example password as 1234.
Can any one help me out with this...
Current code for approval and lock:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">Sub Approval()
Dim found As Range 'define variables
Dim SelectedFileID As String
Dim Lastrow As Long
Dim i As Long
SelectedFileID = Sheets("View_Form").Range("SelFileID").Value 'get the currently selected File ID
Application.DisplayAlerts = False
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").Unprotect Password:="1234" 'change the password to whatever you wish, this unlocks the sheet
Sheets("Tracker").Cells(found.Row, 363).Value = "Approved" 'change the value of the row it was found, but column 226 which is column HR
Sheets("Tracker").Range("A1:MY1000").Cells.Locked = False 'keeps range unlocked
Lastrow = Sheets("Tracker").Cells(Sheets("Tracker").Rows.Count, "A").End(xlUp).Row
For i = 3 To Lastrow
If Sheets("Tracker").Cells(i, 363).Value = "Approved" Then
Sheets("Tracker").Rows(i).Cells.Locked = True
End If
Next i
Sheets("Tracker").Protect Password:="1234" 'protect the sheet after updating to Approved on Column HR
Else
MsgBox "ID not found in Sheet Tracker!", vbInformation 'if not found then show message
End If
ActiveWorkbook.Save '---------------Save workbook
Application.DisplayAlerts = True
End Sub</code>
In "View_Form" sheet we select File ID and all the data relevant to it get displayed and if all looks good we Click macro button "Approved" and the text "Approved" gets in the Column MY adjacent to the selected file ID else it would be blank.
It runs only once and then shows error as File ID not found. I want to restrict. That is if the MY cell contains text "Approved" that particular row from A:MY should get locked or should restrict the user from editing.
Should enable user to edit after using password to unprotect sheet say for example password as 1234.
Can any one help me out with this...
Current code for approval and lock:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">Sub Approval()
Dim found As Range 'define variables
Dim SelectedFileID As String
Dim Lastrow As Long
Dim i As Long
SelectedFileID = Sheets("View_Form").Range("SelFileID").Value 'get the currently selected File ID
Application.DisplayAlerts = False
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").Unprotect Password:="1234" 'change the password to whatever you wish, this unlocks the sheet
Sheets("Tracker").Cells(found.Row, 363).Value = "Approved" 'change the value of the row it was found, but column 226 which is column HR
Sheets("Tracker").Range("A1:MY1000").Cells.Locked = False 'keeps range unlocked
Lastrow = Sheets("Tracker").Cells(Sheets("Tracker").Rows.Count, "A").End(xlUp).Row
For i = 3 To Lastrow
If Sheets("Tracker").Cells(i, 363).Value = "Approved" Then
Sheets("Tracker").Rows(i).Cells.Locked = True
End If
Next i
Sheets("Tracker").Protect Password:="1234" 'protect the sheet after updating to Approved on Column HR
Else
MsgBox "ID not found in Sheet Tracker!", vbInformation 'if not found then show message
End If
ActiveWorkbook.Save '---------------Save workbook
Application.DisplayAlerts = True
End Sub</code>
