VBA to prompt when clicked on protected cell

Manuprasad

New Member
Joined
May 24, 2016
Messages
39
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"
Code:
[/COLOR]Private Sub Worksheet_Change(ByVal Target As Range)
Dim worksh As Integer
Dim worksheetexists As Boolean
Dim str1 As String
with ThisWorkbook.Sheets("SubSheet")
  If UCase$(Range("E30").Value) = "YES" Then
    .Range("E20:I3019").Locked = False
  Else
    .Range("E20:I3019").Locked = True
  End If
end with
End If

Following code on "Subsheet"
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("E20:I3019").Locked = True Then
If Intersect(Target, Range("$E$19:$I$3000")) Is Nothing Then Exit Sub
MsgBox "Please select the appropriate dropdown on ARF Sheet"
With Application
    .EnableEvents = False
    .Undo
    .EnableEvents = True
End With
End If
End Sub

But my concern is Its throwing prompt when cells are unlocked/enabled. I want it to throw a prompt when cells are locked/disabled. Please help
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Forum statistics

Threads
1,136,260
Messages
5,674,681
Members
419,520
Latest member
Jennifer4Dillon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top