How disable an Excel dialog box?
 

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!

How disable an Excel dialog box?
 

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" wrote in message
...
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!

How disable an Excel dialog box?
 

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

How disable an Excel dialog box?
 

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
...
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

How disable an Excel dialog box?
 

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" wrote in message
...
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
...
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