Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Disable Customize dialog box | Excel Programming | |||
disable save as dialog box | Excel Programming | |||
Disable Auto Refresh Dialog | Excel Programming | |||
Disable Query Refresh warning dialog box in Excel 2003 | Excel Discussion (Misc queries) | |||
Disable Saveas Dialog | Excel Programming |