My entire spreadsheet is locked/password protected in
such a manner that users cannot select any locked or unlocked
cells.

However, if a user double clicks on any cell, Excel will display
a dialog box that says the following:

"The cell or chart you are trying to change is protected and
therefore read-only!...." etc, etc....

Is it possible to disable this particular dialog box so I can display
my own custom userform instead?? Can I somehow place some
code in the "Worksheet_BeforeDoubleClick()" event subroutine
to tell Excel to display my own custom userform??

Thank you everyone!

In the worksheet module (or adapted in ThisWorkbook)

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
If Target.Parent.ProtectContents Then
If Target.Locked Then
Cancel = True
MsgBox "don't touch"
End If
End If

End Sub

But no way to intercept if user starts typing in a protected cell

Regards,
Peter T

"Robert Crandal" <(E-Mail Removed)> wrote in message
news:mdYgn.4184$(E-Mail Removed)...
> My entire spreadsheet is locked/password protected in
> such a manner that users cannot select any locked or unlocked
> cells.
>
> However, if a user double clicks on any cell, Excel will display
> a dialog box that says the following:
>
> "The cell or chart you are trying to change is protected and
> therefore read-only!...." etc, etc....
>
> Is it possible to disable this particular dialog box so I can display
> my own custom userform instead?? Can I somehow place some
> code in the "Worksheet_BeforeDoubleClick()" event subroutine
> to tell Excel to display my own custom userform??
>
> Thank you everyone!
>
>

Not that I've seen.

But you could prohibit the selection of locked cells so that the user won't even
see the message.

Look in the Tools|Protection|protect sheet dialog (xl2002 or higher).

Or do it in code (any version):

Option Explicit
Sub auto_open()
Dim wks As Worksheet
Set wks = ThisWorkbook.Worksheets("Sheet1")

With wks
.EnableSelection = xlUnlockedCells
.Protect Password:="hi"
End With

End Sub

By naming it Auto_Open and putting it in a general module, it'll run whenever
excel opens this file (and the user allows macros to run).



Robert Crandal wrote:
>
> My entire spreadsheet is locked/password protected in
> such a manner that users cannot select any locked or unlocked
> cells.
>
> However, if a user double clicks on any cell, Excel will display
> a dialog box that says the following:
>
> "The cell or chart you are trying to change is protected and
> therefore read-only!...." etc, etc....
>
> Is it possible to disable this particular dialog box so I can display
> my own custom userform instead?? Can I somehow place some
> code in the "Worksheet_BeforeDoubleClick()" event subroutine
> to tell Excel to display my own custom userform??
>
> Thank you everyone!

--

Dave Peterson

Thank you very much Peter....I have a second question now.

In the line that says "MsgBox 'don't touch'", how can I make
the message box show the current row & column that was double clicked???

Thank u



"Peter T" <peter_t@discussions> wrote in message
news:(E-Mail Removed)...
> In the worksheet module (or adapted in ThisWorkbook)
>
> Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
> Cancel As Boolean)
> If Target.Parent.ProtectContents Then
> If Target.Locked Then
> Cancel = True
> MsgBox "don't touch"
> End If
> End If
>
> End Sub
>
> But no way to intercept if user starts typing in a protected cell
>
> Regards,
> Peter T
>

Dim sMsg As String
' code
With Target
sMsg = "Address:" & .Address(0, 0) & " Row:" & .Row & " Col:" & .Column
End With
MsgBox sMsg

Regards,
Peter T

"Robert Crandal" <(E-Mail Removed)> wrote in message
news:C2Zgn.8053$(E-Mail Removed)...
> Thank you very much Peter....I have a second question now.
>
> In the line that says "MsgBox 'don't touch'", how can I make
> the message box show the current row & column that was double clicked???
>
> Thank u
>
>
>
> "Peter T" <peter_t@discussions> wrote in message
> news:(E-Mail Removed)...
>> In the worksheet module (or adapted in ThisWorkbook)
>>
>> Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
>> Cancel As Boolean)
>> If Target.Parent.ProtectContents Then
>> If Target.Locked Then
>> Cancel = True
>> MsgBox "don't touch"
>> End If
>> End If
>>
>> End Sub
>>
>> But no way to intercept if user starts typing in a protected cell
>>
>> Regards,
>> Peter T
>>
>