VBA to throw a prompt when clicked on protected cell

Question

I have Workbook with both Sheet/workbook is protected. I have a code to lock/disable certain range of cells when the drop-down value "no" And unlock/enable when value of drop down is "yes" Whereas, drop-down value and cells I would like to disable are on different sheets.

Dropdown on "Main Sheet" Range of cells on "Sub Sheet"

I also need to throw a prompt to user when he clicks on protected range and when the value is set to "No".

I am using following code on "Main Sheet"

Private Sub Worksheet_Change(ByVal Target As Range)
Dim worksh As Integer
Dim worksheetexists As Boolean
Dim str1 As String

If UCase$(Range("E30").Value) = "YES" Then
               Sheets("SubSheet").Select
               Sheets("SubSheet").Range("E20:I3019").Locked = False
               Sheets("SubSheet").Range("E20:I3019").Activate
           Else
                Sheets("SubSheet").Range("E20:I3019").Locked = True
End If
End Sub

Following code on Sub Sheet

Private Sub WorkBook_SheetChange(ByVal sh as Object, ByVal Target as Range)
If Intersect (Target, sh.Range("$E$19:$I$3000")) Is Nothing Then Exit Sub
MsgBox "Please select the appropriate dropdown on MAIN Sheet " & Target.Address
With Application
    .EnableEvents = False
    .UnDo
    .EnableEvents = True
End With
End Sub

Not sure, where am I going wrong as Its not throwing prompt when user clicks on protected cells.

Ary Ary · Accepted Answer · 2016-10-05T13:24:19

First. You should remove the Sheets("SubSheet").Select. If you running your code and your are not inside the sheet, it could occur an error. try to do:

with ThisWorkbook.Sheets("SubSheet")
  If UCase$(Range("E30").Value) = "YES" Then
    .Range("E20:I3019").Locked = False
    .Range("E20:I3019").Activate
  Else
    .Range("E20:I3019").Locked = True
  End If
end with

Second. You don't return the target range. I mean your Private Sub WorkBook_SheetChange waits for a ByVal Target as a parameter and your Private Sub Worksheet_Change returns any value.It should be a function returning the range or the cell you have selected for me.

EDIT:

with ThisWorkbook.Sheets("SubSheet")
  If UCase$(Range("E30").Value) = "YES" Then
    .Range("E20:I3019").Locked = False
  Else
    .Range("E20:I3019").Locked = True
     WorkBook_SheetChange Range("E20:I3019")
  End If
end with

And

Private Sub WorkBook_SheetChange(ByVal Target as Range)
    If Intersect (Target, Range("$E$19:$I$3000")) Is Nothing Then Exit Sub
    MsgBox "Please select the appropriate dropdown on MAIN Sheet " &    Target.Address
    With Application
       .EnableEvents = False
       .UnDo
       .EnableEvents = True
    End With
 End Sub

VBA to throw a prompt when clicked on protected cell

1 Answers