(*Challenging!*) Protect two range's data validation and change in priority order

[ld2x07]

Hi all,

I've spent the last week on this problem and I have come to wiser minds than mine. First post! :hi:

Here's an outline of the problem:


Column K has a yes/no drop-down data validation. Range L5:P201 has another drop-down list,
I need to protect both ranges from removal of their validation (e.g. from pasting over the top)
I need the second range (L:P) to only be allowed input if the first range has the value "Yes".


I have the following code to protect the validations but I can't seem to get it to work. I also don't know how to add in the 'Yes' condition. And if at all possible if you can add a message to that latter condition saying something like "Operational cancelled, you need to put a value in column K first".

Any help would be very greatfully received!!


Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Does the validation range still have validation?
If Not HasValidation(Range("L5:P201")) Then RestoreValidation
If Not HasValidation(Range("K5:K201")) Then RestoreValidation
End Sub
Private Sub RestoreValidation()
Application.EnableEvents = False
'turn off events so this routine is not continuously fired
Application.Undo
Application.EnableEvents = True
'and turn them on again so we can catch the change next time
MsgBox "Your last operation was canceled." & _
"Please click on the cell and select and option.", vbCritical
End Sub
Private Function HasValidation(r) As Boolean
' Returns True if every cell in Range r uses Data Validation
On Error Resume Next
Debug.Print r.Validation.Type 'don't care about result, just possible error
If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub

[snb]

Use a userform instead.

[MINCUS1308]

This doesnt address your entire problem but i hope it will help you.


Sub Check_K_ForYes()
'You need to tell it what row to look at
'so set the MyRow variable = to the active row


If Cells(MyRow, 11).Value = "Yes" Then
MsgBox "Row Number: " & MyRow & " Has a 'Yes' in column K"
Else
MsgBox "Row Number: " & MyRow & " Does not have a 'Yes' in column K"
End If


End Sub


Note: This only checks for a 'Yes' [NOT 'yes' or 'YES' or 'YEs' or any other combination you come up with]

[MINCUS1308]

OK.
If i'm understanding your situation correctly:

Column K contains a yes, no, or a blank
Range L:P has a drop down list
Your data table runs from row 5 to 201.
You dont want people to be able to edit/ paste over top of this area
and you only want the active row range L:P to accept inputs if column K is 'Yes'
you would like a message to appear if someone trys to input when column K is not 'Yes'

If this is the case, you should attack the protection of your ranges on a row by row basis.
I would recommend utilizing OnDoubleClick events

As for your message box the following code will do it:

MsgBox "Operational cancelled, you need to put a value in column K first."

[MINCUS1308]

Sorry, I can help you solve your problem butttttttttt I kinda have to do it in pieces...
Im at work :p



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 11 Then
If Target.Value = "Yes" Then
'The event: you want to allow changes to range L:P in this row
Else
'The event: that column K is either 'No', blank, or something else
End If
Else
'The event the user makes a change to anyother column in the worksheet
End If
End Sub

[ld2x07]

I don't actually have any VBA skill myself, the code I provided was taken from a forum online. No idea what a userform is.

If an excel master can get my code working and splice Mincus' Yes code into it I think we're there!

[MINCUS1308]

This code will go in the Sheet#("Codename") Module

Obviously its not finished yet



'Start by protecting the entire worksheet
'locking only the desired ranges L:P
'We will unprotect these cells as we go




'At this Point All cells L:P will be locked unless column K is "Yes"
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 11 Then 'Check if they are changing column K
If Target.Value = "Yes" Then 'Check if they are changing column K to "Yes"
LRow = "L" & Target.Row 'Create a String that defines the Left bound of the required range
PRow = "P" & Target.Row 'Create a String that defines the Right bound of the required range
Range(LRow, PRow).Select 'Select The required range L:P of active Row
'Now Set protection as required
Else 'The event: that column K is either 'No' or blank or something else
'We will need to re-protect the range of cells L:P
End If
End If
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.DisplayAlerts = False 'Dont wata have the system constantly telling us the WS is protected
If (Target.Column >= 12 And Target.Column <= 16) Then 'If the user selects any Cell L:P
If Cells(Target.Row, 11).Value <> "Yes" Then 'Check if Column K of the active Row is NOT "Yes"
MsgBox "Operation cancelled, you need to put a value in column K first."
'These cells will already be locked they wont be able to make any changes anyways
End If
End If
Application.DisplayAlerts = True
End Sub

[MINCUS1308]

ld2x07 (http://www.vbaexpress.com/forum/member.php?55092-ld2x07) - Is there a chance you could attach your file so that I can test and finish this code?

[MINCUS1308]

Mine Works :)

Pasted in: Sheet1(Sheet1)


Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect
If Target.Column = 11 Then 'Check if they are changing column K
LRow = "L" & Target.Row 'Create a String that defines the Left bound of the required range
PRow = "P" & Target.Row 'Create a String that defines the Right bound of the required range
If Target.Value = "Yes" Then 'Check if they are changing column K to "Yes"
Range(LRow, PRow).Select 'Select The required range L:P of active Row
Selection.Locked = False 'un-protecting the range of cells L:P
Else 'The event: that column K is either 'No' or blank or something else
Range(LRow, PRow).Select 'Select The required range L:P of active Row
Selection.Locked = True 're-protecting the range of cells L:P
End If
End If
ActiveSheet.Protect
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.DisplayAlerts = False 'Dont wata have the system constantly telling us the WS is protected
If (Target.Column >= 12 And Target.Column <= 16) Then 'If the user selects any Cell L:P
If Cells(Target.Row, 11).Value <> "Yes" Then 'Check if Column K of the active Row is NOT "Yes"
MsgBox "Operation cancelled, you need to put a value in column K first."
'These cells will already be locked they wont be able to make any changes anyways
End If
End If
Application.DisplayAlerts = True
End Sub


Pasted in: ThisWorkbook


Private Sub Workbook_Open()
ActiveSheet.Protect
End Sub

[ld2x07]

12435

Happy to, thank you :yes

[ld2x07]

Tried it as above but doesn't seem to do anything..

[MINCUS1308]

If you can send me your file i can set this up for you

[ld2x07]

Thank you. The file is in my post above (05:57am)

Here too: 12439

[MINCUS1308]

Sorry for the delay - lots of meetings today.
This should do what your looking for

12441

let me know