Lock Range of Cells Using Command Button

[jdillon]

Hello,

I'm new to MrExcel and could use some help with the following situation:

Person 1 enters data in single row, various columns A through O, then hits a "process" button that I've inserted using ActiveX Controls. Their username and date/timestamp populates into that row, column Q.

Person 2 goes into the worksheet and verifies that data by clicking a "verify" button(also, ActiveX Controls). Their username, date/timestamp populates into same row, column S.

Person 1's data, including username, date/timestamp, needs to be locked down, even if some cells are left blank. When person 2 verifies the data, their username, date/timestamp needs to be locked down.

Is it possible to add this functionality to the existing button commands?

Secondly, I need manager-level personnel to be able to clear the data in a row. I'm hoping to just have this as a button to clear the row that's accessible through a password.

Any and all help would be appreciated! Unfortunately this is going to be needed for a rather large workbooks, recreated on hundreds of rows.

 

[DanteAmor]

I attached the codes for the 3 buttons

Sub [COLOR=#0000ff]Process_Click[/COLOR]()
    Dim wRow As Double
    
    wRow = ActiveCell.Row
    
    'VALIDATIONS
    If Cells(wRow, "A").Value = "" Then
        MsgBox "You need to enter data in column A"
        Exit Sub
    End If
    '
    'OTHER VALIDATIONs
    
    '
    'Unprotec sheet to put data
    ActiveSheet.Unprotect "abc"
    Cells(wRow, "Q").Value = Application.UserName & " " & Now()
    Range("A" & wRow & ":Q" & wRow).Locked = True
    ActiveSheet.Protect "abc"


End Sub


Sub [COLOR=#0000ff]Verify_Click[/COLOR]()
    Dim wRow As Double
    
    wRow = ActiveCell.Row
    'VALIDATIONS
    If Cells(wRow, "Q").Value = "" Then
        MsgBox "The process of person 1 is missing"
        Exit Sub
    End If
    
    'Unprotec sheet to put data
    ActiveSheet.Unprotect "abc"
    Cells(wRow, "S").Value = Application.UserName & " " & Now()
    Range("A" & wRow & ":S" & wRow).Locked = True
    ActiveSheet.Protect "abc"


End Sub


Sub [COLOR=#0000ff]Manager_Click[/COLOR]()
    Dim wRow As Double, wPass As Variant
    
    wRow = ActiveCell.Row
    'VALIDATIONS
    wPass = InputBox("Enter Password", "MANAGER-LEVEL")
    If wPass = "12345678" Then
        'Unprotec sheet to put data
        ActiveSheet.Unprotect "abc"
        Range("A" & wRow & ":S" & wRow).Value = ""
        ActiveSheet.Protect "abc"
    End If
End Sub

 

[jdillon]

Thank you, Dante. I appreciate your post. My apologies for not getting back to this sooner.

A couple points of clarification from my original post, which I hope you can help with:
The Process button is specific to one row of data. I'll need to have about 4,000 rows worth of Process buttons, specific to the data that Person 1 entered for that row, in columns A:O.
Same goes for the Verify button, it's specific to Person 2 verifying that single row of data.
Same goes for the Manager Override button. I only want to unlock that single row, in which case the entire row can revert to everything in that specific row being editable again.

Once again, any and all help is appreciated! Thank you!

 

[DanteAmor]

Thank you, Dante. I appreciate your post. My apologies for not getting back to this sooner.

A couple points of clarification from my original post, which I hope you can help with:
The Process button is specific to one row of data. I'll need to have about 4,000 rows worth of Process buttons, specific to the data that Person 1 entered for that row, in columns A:O.
Same goes for the Verify button, it's specific to Person 2 verifying that single row of data.
Same goes for the Manager Override button. I only want to unlock that single row, in which case the entire row can revert to everything in that specific row being editable again.

Once again, any and all help is appreciated! Thank you!

It is not functional to have a button for each line, it is advisable to select the line and process it.
The button does not tell you what line it is on, you would have to add code to know which line the button is on or create a macro for each button. But if you delete the row that macro does not work anymore, but if you add ... Anyway, it's not practical.

 

[jdillon]

Thanks for your insight, Dante.