SelectionChange Select Current Row of Range and Lock

[adventagious]

I am using VBA enhanced Excel workbooks for training event registration internally for our company. These are protected workbooks with only certain fields available for editing, enabling a few dozen training managers to enroll their people into classes. There is a predetermined number of seats in the class.

I am currently restructuring how the workbooks function. Per the attached picture, Column K is using a data validation list with two options. The options are Available and Reserved. I want a training manager to enter their employee's data in columns L through O and then change the status from Available to Reserved.

When that change is made, I want the current row of columns K thru O to be selected and locked, protecting the "reservation" from being edited by anyone else except our training staff when the sheet is unprotected. I have the [vba]UserInterFaceOnly:=True[/vba] option applied to the sheets in the workbook to allow macros to run while the sheet is protected.

The pertinent cells in column K (K5:K484) have been made a range with the name LockStatusRange. The pertinent cells in columns K thru O (K5:O484) have been made into a range with the name TMinputRange.

Based on what I've researched, my thought is to use SelectionChange on the activecell in LockStatusRange (K) to fire the vba to select and lock the current row of TMinputRange (K thru M). Am I on the right track here? or should this be approached differently?

I've implemented a number of Excel VBA modifications over the past few years, but it has been a little while, as I've been working with Access and SharePoint more lately. I'm having a hard time wrapping my mind around how to build this.

I'd love some guidance on how to code this correctly. Thanks a lot.

[Bob Phillips]

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Me.Range("LockStatusRange")) Is Nothing Then

If Target.Value = "Reserved" Then

Target.Offset(0, 1).Resize(1, 4).Locked = True
End If
End If
End Sub[/vba]

This is worksheet event code.
To implement it, select the sheet tab, right click, and
select View Code.
Paste this code into the code module that opens in the
VBIDE.
Then close the VBIDE and test it in Excel.

[adventagious]

Thanks xld. Partial success so far.

I am using data validation with an in-cell drop-down list, containing both choices: Available and Reserved. If I choose Reserved from that drop-down list, no change occurs. If I delete the data validation list from that cell and just paste in the word Reserved, the sub runs and the row of the range locks.

Will this not work using the data validation list? Does it read both values in the cell?

Thanks.

[Bob Phillips]

What version of Excel, surely not that old?

[adventagious]

2007.

Sorry. I should have posted that up front.

[Kenneth Hobs]

Obviously, you would need to add your Protect and Unprotect code to xld's example in your case.

Here is what I did:
[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
Dim pw As String, r As Range
pw = "ken"
Set r = Intersect(Target, Range("LockStatusRange"))
If r Is Nothing Then Exit Sub
If r.Count <> 1 Then Exit Sub

ActiveSheet.Unprotect pw
If Target.Value2 = "Reserved" Then
Range("K" & Target.Row, "M" & Target.Row).Locked = True
Else
Range("K" & Target.Row, "M" & Target.Row).Locked = False
End If
ActiveSheet.Protect pw, UserInterfaceOnly:=True
End Sub[/VBA]

Small examples usually help us help you faster and better. Not all forums let you attach examples.

[adventagious]

I had applied the UserInterfaceOnly to all the worksheets upon open, and the validation cell would not be locked to begin with (only after the SelectionChange code ran), so I didn't think I needed to protect and unprotect and apply the UserInterfaceOnly change again.

I also noticed that your list was self-contained in the Validation list source field. I had been referring to an external source on the spreadsheet. I changed to what you were using and that is what in fact made xld's code run.

I went with your code mod though and modded it a bit further so that, once the worksheet is unprotected, changing the validation from Reserved to Available will unlock the K:O current row fields, but not reprotect the document (this for admin mgmt). They will run a separate macro to reprotect the sheet once done.

[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
Dim pw As String, r As Range
pw = "custaff"
Set r = Intersect(Target, Range("LockStatusRange"))
If r Is Nothing Then Exit Sub
If r.Count <> 1 Then Exit Sub
If Target.Value2 = "Available" Then
Range("K" & Target.Row, "O" & Target.Row).Locked = False
Else
ActiveSheet.Unprotect pw
If Target.Value2 = "Reserved" Then
Range("K" & Target.Row, "O" & Target.Row).Locked = True
End If
ActiveSheet.Protect pw, UserInterfaceOnly:=True
End If
End Sub[/VBA]

Works great! Thanks a lot Ken and xld.