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!


0
Robert
2/23/2010 9:59:44 PM
excel.programming 6508 articles. 2 followers. Follow

4 Replies
1835 Views

Similar Articles

[PageSpeed] 2

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" <nobody@gmail.com> wrote in message 
news:mdYgn.4184$XI1.3174@newsfe04.iad...
> 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!
>
> 


0
Peter
2/23/2010 10:20:06 PM
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
0
Dave
2/23/2010 10:25:18 PM
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:OtLAXZNtKHA.3904@TK2MSFTNGP02.phx.gbl...
> 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
>

0
Robert
2/23/2010 10:56:32 PM
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" <nobody@gmail.com> wrote in message 
news:C2Zgn.8053$Cw3.4931@newsfe21.iad...
> 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:OtLAXZNtKHA.3904@TK2MSFTNGP02.phx.gbl...
>> 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
>>
> 


0
Peter
2/24/2010 9:57:10 AM
Reply:

Similar Artilces:

Disable BU
A really quick one for you. If I disable a BU in MSCRM, does the corresponding OU in Active Directory become disabled? I think the answer probably 'No' as I actually cannot find a way to disable the OU manually and therefore cannot check, but if anyone has a definitive answer I would be grateful. Regards Peter John - thanks for your help. Regards Peter >-----Original Message----- >No, disabling BU in CRM will not disable corresponding OU in the AD, just like dissabling CRM user in CRM will not siable the corrsponding user in AD. > >"Pete" wrote: > &...

Message Box 02-08-10
Hi Apologies, forgot to add subject, so not sure if it will be displayed, posting again Is it possible to set a message box with customs button texts? If possible how? When a button called Close is pressed, I want the user to be able to select to close just the Database or the Database and Application? Many thanks I showed how in http://www.vb123.com/kb/200505_ds_msg.htm -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "Alex Hammerstein" <aph@misnet.co.uk> wrote in message news:C7960CEF.1562B%aph@misne...

how can disable service with bat file
Hi, Please tell me, how can i disable service with bat file. What service? "Vijay" wrote: > Hi, > > Please tell me, how can i disable service with bat file. > Please tell me, how can i disable service with bat file. That's funny ... weren't we just talking about Permissions? :-) Anyway, the command to control a Windows Service (enable, start, stop, disable, etc) is SC.EXE. Run SC /? at a command prompt to see the syntax. Put the necessary SC command in a batch file, and you are all set. Hope it helps, Andrew -- amclar at ...

Excel 2002 can't read files
All of a sudden, my excel 2002 reports all my files as "unable to read". They can be opened on other computers. I've tried repairing Office and running a virus scan, but it still won't work. Even new files won't open again after I save them once. Does this sound familiar to anyone? Disable the Office Plugin that comes with Symantec/Norton AntiVirus. See http://support.microsoft.com/?kbid=329820 "How to use Office programs with the Norton AntiVirus Office plug-in" for more info. "Batty boy" wrote: > All of a sudden, my excel 2002 reports...

Print Preview "Prev Page" but is always disabled
Hello, I have created an MFC application and have added Printing and Print Preview capability. The only problem i have is that the Prev Page button on the Print Preview toolbar is always disabled. The "Two Pages" button and "Next Page" are enabled and work correctly. I am setting the CPrintInfo::SetMaxPage() and CPrintInfo::SetMinPage() and CPrintInfo::m_nCurPage values in OnPreparePrinting() but this doesnt seem to make a difference. Is their anything else that i can try which will enable this button? Thanks Andy ...

Can Excel trigger Outlook reminder?
I hope I'm asking this question in the right forum. I would like to know if it is possible to create a basic spreadsheet where one of the columns is for due date and when that due date comes up a reminder is generated from Outlook? I think I understood correctly from a Google search that it can be done in Visual Basic but I have no idea how to do it. I'm also hoping it would not be complicated b/c I have not used VB. If that can't be done, how could I work it that the cell would change to red with due date comes? Thanks. A little more info on the spreadsheet I...

Splitting cells in Excel 2007
Hello Microsoft, I've spent about an hour on various Internet sites and discussing this in forums. Many seem to know it's not possible, but nobody has told me why it's not possible. The question has been asked as far back as 1999, from what I've been seeing. How does one split a single cell either vertically or horizontally? I don't mean the data within the cell, but the cell itself. Many "solutions" seem to use techniques of adding new rows or columns and doing some creative merges to all the cells affected. The spreadsheet I want to change...

Export to Excel
Can anyone give me some help on export records to Excel please. I have a table were each field is 254 chars and I have chopped up some data. I would like to put it back together as I export it to Excel. Like this: part1 part2 part3 part4 etc. Export all into Cell 1 Thanks in advance! Bonnie On Mon, 11 Jan 2010 14:26:01 -0800, Bon <Bon@discussions.microsoft.com> wrote: Rather than exporting a table, export a query. In this query you concatenate the fields: select part1 & part2 & part3 & part4 as AllParts from myTable -Tom. Microsoft Access...

excel 2000 vs 2003: drop-down box locking issues
Hi, I've been using drop down list boxes in an excel 2003 worksheet. In 2003, I am able to lock the cell with the drop down box and protect the worksheet, which then prevents anyone from selecting a new value from the drop down boxes and changing the contents of that cell. However, when I try the same procedure in excel 2000, the drop down box never locks (though it's property says it's locked), and can still allow new selections from the box. Does anyone know of a workaround for this, or is this just a flaw in 2000? Thanks! In Excel 2000 and earlier versions, you can...

disable macro warning
I added macro which I then deleted. Every time I enter system it asks me if I want to disable or enable macros - how do I turn off this warning screen upon entering system when I have no macros in the worksheet? You need to remove the module that contained the macro. Press ALT+F11, go to View > Project Explorer (if needed), right-click on the module(s), and remove it. Save. HTH Jason Atlanta, GA >-----Original Message----- >I added macro which I then deleted. Every time I enter >system it asks me if I want to disable or enable macros - >how do I turn off this warnin...

pictures on email will not forward with text. just a white box
when i forward an email with pictures on it - not attachments - it will not enclude the picture. just a white box where the pictures were. thanks ...

Excel in multiple sessions
A collegue is attempting to access data using vlookup in a spreadsheet from a second spreadsheet which is opened in a separate session on his workstation. It appears that multiple sessions of excel can't 'see' each other. Collegue needs to have multiple sessions as he uses lots of spreadsheets with LOTS of formulas. Attempting to open these sheets in single session usually results in crashes and lost work. Does anyone out there know anything about this? Using Excel 2000 on Windows 2000 Professional sp4. Thanks =vlookup() worked ok for me--but by having the workbooks in separ...

Disable sending Email
In Exchange 5.5 in an NT Domain, is there a way to disable an user from sending email? He should be able to open and read his email but not be able to send out email untill permitted. Thanks On Wed, 16 Mar 2005 15:01:03 -0800, "SI" <SI@discussions.microsoft.com> wrote: >In Exchange 5.5 in an NT Domain, is there a way to disable an user from >sending email? He should be able to open and read his email but not be able >to send out email untill permitted. > >Thanks You can do this on Internet mail by setting the Delivery Restriction on the IMS to deny mail...

Disable Cached Mode
Hi all! Is there any way to disable Cached Mode (CM) on Exchange 2003? Sure, I can do it with GPO, but some computers can be out of scope of this GPO and then they will be able to turn on the CM in their Offce 2003. But CM is critical security risk and I have to disable it. At http://office.microsoft.com/en-us/assistance/HP010000781033.aspx?Product=out2003 I can read: "Exchange server administrators have the ability to disable this feature in Outlook. For more information, contact your Exchange server administrator. Additional information for administrators is available in the Mi...

Excel, how to send to mulitple e-mails
I have an excel spreadsheet for tracking names, phone #'s and e-mails and I want to be able to capture all of the e- mails and send out stuff without having to copy and paste each e-mail individually (looking for shortcut). Any help would be appreicated. Thanks. John Hi John Look here http://www.rondebruin.nl/sendmail.htm see the Tips pages for using addresses in cells -- Regards Ron de Bruin http://www.rondebruin.nl "John" <anonymous@discussions.microsoft.com> wrote in message news:986101c49741$14336f00$a601280a@phx.gbl... >I have an excel spreadsheet for trac...

How to remove indent in table cell from Excel
I copied part of an XLS spreadsheet to create a Word table. The text in many cells is indented in the original, but I need to make these flush left within the cell. But I can't find a place within Word where this indent is shown: it's not in the table cell specs nor in the paragraph definition. Is there a way to unindent? My purpose is to do this within VBA, in case that changes the answer. -- Christopher Brewster Lockheed Martin, Eagan MN Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/ms-word-general/200911/1 Dim i As Long, j As Long Wit...

SMTP being disabled
HI Exchange 2003 / Server 2003 I'm just having a world of problems with Exchange all of a sudden... It's been up and running about 2 months but all of a sudden it's going nuts... The SMTP service is being stopped and set to disabled. This is happening each morning around 1 AM. The only thing I can find in the event logs is event 7035 - The SMTP service was successfully sent a stop control and then Event 7040 - The start type of SMTP service was changed from auto start to disabled For the life of me, I can't find what is originating these commands.... I thought maybe t...

Enable/Disable Macro Pop-Up
In the older version of Office when you opened an excel or other office document that had a macro in it - you would get a pop up and the opportunity to enable or disable it. Is there a way to get that same pop-up in 2007? See Help, macro setting Change macro security settings in Excel juamig wrote: > In the older version of Office when you opened an excel or other office > document that had a macro in it - you would get a pop up and the opportunity > to enable or disable it. Is there a way to get that same pop-up in 2007? > Right, someone else told me the...

how do you export excel files to Quickbooks Simple edition
I'm a newbie....I need to know if you can export invoices that are on EXCEL templates into Quickbooks invoice file or sales receipt file. Thanks for any help on this quest. Dianna In quickbooks go to file import excel "Newbie Dianna" wrote: > I'm a newbie....I need to know if you can export invoices that are on EXCEL > templates into Quickbooks invoice file or sales receipt file. > > Thanks for any help on this quest. > Dianna ...

Open and close Dialog windows
Hello, I've got a dialog based application which performs a long lasting task for which the user has to wait. I would like to show the user a message that something is happening and I would like to close the message automatically when the task is finished. Because I've little experience with mfc I would like to ask how I can manage this and if there is an example for doing it ? Is there a faq for this group ? Thanks in advance, Ralf. "Ralf Erdmann" <ralf.erdmann@web.de> wrote in message news:403f7fa8.14652421@news.t-online.de... > Hello, > I've got a di...

Excel scatter plot
Suppose I have a sheet with three columns and the value is like the following: ID X Y =========== A 1 1 B 2 3 C 5 4 I can easily make a scatter plot of X vs Y, but I am struggling to find a way to automatically mark each spot with the corresponding ID. Something like the below: 6 | | 4 | .C | .B 2 | | .A 0 |------------- 2 4 6 Does anyone of you know how to do this automatically (by which I mean I do not need to manually do it with something like text box etc.)? Thank you very much! Sam S...

Excel Rejects Formula (Shaded Cells)
Hi I have taken an existing Excel 2002 Invoice and used it as a starting point to create new invoice. Everything seems to work fine, except in the totals column I cannot enter any formulae. I am trying to use the following formula : =IF(B26<>"",B26*I26,"") Basically, if there is a value in cell B26, then cell B26 is multiplied by cell I26 to give the product. The problem is I get the following error message when I try to enter the formula: "The shaded cells contain formulas and are automatically calculated by Excel. DO NOT enter any information i...

how do i resize column letters in excel 2007
I have two worksheets in different workbooks. They both have Calibri 10 as the font. Yet in one workbook the column letters and row numbers are larger than in the other one. How do I change this? Could be the zoom is different in each file. -- Biff Microsoft Excel MVP "Debby" <Debby@discussions.microsoft.com> wrote in message news:95F138EC-4F62-4CFB-95ED-178BE7481CDE@microsoft.com... >I have two worksheets in different workbooks. They both have Calibri 10 as > the font. Yet in one workbook the column letters and row numbers are > larger > th...

Protecting sheet disables Grouping buttons
I am delivering a worksheet to a client, and have protected it. As well as stopping people changing the data, though, it also stops them using the Grouping buttons. How can I enable grouping on a protected sheet? If you already have the outline applied, you can protect the worksheet in code (auto_open/workbook_open??). Option Explicit Sub auto_open() With Worksheets("sheet1") .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True .EnableAutoFilter = True End With End Sub (I wasn't sure what caused your outlining--...

Drop Down Box #7
Greetings, I have an Excel doc with drop down boxes from the control toolbox. I was able to affect the properties of these boxes by right clicking on them and going ino the dialogue box for formatting the controls. Now when I try to do it, it doesn't give me the right click menu. Is there some method in Excel to reactivate the right click menu on these controls? Perhaps some sort of template mode? Thank you. Rod You can try this: Press Alt+F11 Press Ctrl+G Type this into the pane that appears at the bottom of the Window and press Enter: Application.CommandBars("Cell")...