Re: Prevent Editing Cells Without Protection WORK_10, This is an old thread. Do not post resurrect old threads and do not post your question in a thread started by someone else - this is known as thread hijacking and is against forum rules (see link in my signature). Re: Prevent changing value in cell Paul, I have a situation, where I want to allow the users to select special cells that they cannot edit, but I don't want to allow ALL locked cells to be selected. So I have to unlock those special cells with the sheet protected, but I want to prevent them from editing those cells.
Here’s a typical scenario: your code modifying a workbook fails because the user types something into a cell. Now how to determine that Excel is in the edit mode?
A non-solution The code below is based on the fact that certain command bar controls in Excel become disabled when you type in a cell. In the UI of Excel 2000-2003, you can find heaps of commandbar controls that become disabled in this scenario. Thank you for sharing your solution. I thought your code might help me in determining the time a user spends in edit mode. However I can’t seem to get this working in Excel 2003 (using the ‘Universal’ solution). I searched around and found out that: 1) Try/Catch/Finally is not supported in VBA 6.X, so I have substituted “On Error” instead. 2) ‘Return’ is not accepted by the compiler, so I have used IsEditing = False or IsEditing = True instead.
3) The code seems to only be fired when I the function IsEditing is called/used. But I need to know the exact time when the user enters the edit mode (and leaves it). The worksheetchange event is only triggered after the cell has been entered. How can I make this code run independent of the user actions? (which seems to be the intent of the code in the first place) Thank you for your suggestions, Sean.
Hello Sean, The solutions above cannot be used to achieve this. Even translated to VB6/VBA. You need to dwell into the Windows API world. The idea is to look for the creation/destruction of the window that Excel creates when you start/stop editing a cell. You can find out how that window is called using Spy (spyxx.exe).
Spy is supplied with all Visual Studio versions even in VS6. Add-in Express uses Windows API as described above to provide events that occur before the user starts editing a cell and after the user finishes editing the cell. This solution is quite old, so I’m not sure if this has always been the case, but in many cases, calling `xlApp.Interactive = false` will cause even more issues. I discovered that in Excel 2013/2016 this line of code will.interrupt. the user if they happen to be performing a number of actions: – Resizing or moving an excel-owned window (the action will be aborted) – In a non-modal excel-owned dialog like the VBA editor (they will be ‘kicked’ back to the main workbook window) – Selecting or moving cells (really, any mouse-drag action) will be aborted. Looks like it’s back to the drawing board to find an unobtrusive way of detecting if the user is busy.
This technology is now available for our custom development services only. Based on the Add-in Express for Office core, it is designed for building custom-tailored Office add-ins with far less coding than you usually have to do. Plus, it includes all Add-in Express features such as True RAD, visual designers, Outlook view and form regions, etc. Extensions: VSTO-based application-level Office add-ins Applications: Outlook, Excel, Word, PowerPoint, Visio, InfoPath Office versions: 2003, 2007, 2010 (x86 and x64) IDE: VSTO 2005 SE, 2008, 2010; VB.NET, C#. Designer for Visual Studio® and WiX Toolset This is an extension for Visual Studio that allows developers to quickly create WiX-based setup projects in a familiar Visual Studio way. The Designer for WiX Toolset lets you forget the plain Windows Installer XML and concentrate on your deployment logic. It integrates several editors with the Visual Studio IDE and provides a set of vdproj designers to configure the file system, registry, user interface, custom actions, launch conditions and more for your setup projects.
This technology is now available for our custom development services only. This visual toolkit allows creating secure, managed, isolated, deployable and version-neutral plug-ins for Outlook Express and Windows Mail. It provides powerful solution templates, Outlook Express - specific components, visual designers and wizards for advanced customization of Outlook Express menus, toolbars, panes and regions. Extensions: Outlook Express plug-ins Versions: Outlook Express 6.x, Windows Mail 6.x IDE: VS 2005, 2008; VB.NET, C#, C/CLI.
The following steps will guide you to unlock all cells in current firstly, lock required cells and ranges, and then protect current worksheet. Please do as follows: 1. Click the Arrow at the upper-left corner of worksheet to select all cells in active worksheet. Right click any selected cell, and select the Format Cell item from the context menu. See below screenshot: 3.
In the Format Cells dialog box, uncheck the Locked option under Protection tab, and click OK button. See above screenshot: 4. Select cells and ranges that you want to lock, right click and select the Format Cell item from the context menu. In the Format Cells dialog box, check the Lock option under Protection tab, and click OK button. See above screenshot: 6. Click Review Protect Sheet to protect current worksheet.
See below screenshot: 7. Enter a password in the blank box in the popping up Protect Sheet dialog box, and click the OK button. And then retype the password into the another Confirm Password dialog box, and click the OK button.
See screenshot above. And now it locks and protects only cells and ranges you selected in the Step 4, while unselect ranges are editable. Lock and protect selected cells from editing with Kutools for Excel. Click the Arrow at the upper-left corner of worksheet to select all cells in active worksheet. Click Enterprise Worksheet Design to activate the Design tab, and then click the Design Selection Unlock.
See screenshot: Then a dialog box comes out to tell you selected cells have been unlocked. Just click the OK button to close it. Select cells and ranges that you want to lock, and click the Design Selection Lock. Then click the OK button to close the popping up Kutools for Excel dialog box. Click the Design Protect Sheet or Review Protect Sheet, and then you can follow.
Lock and protect selected cells from viewing by encrypting. Select the cells that you want to lock and protect, and click the Enterprise Encrypt Cells. In the opening Encrypt Cells dialog box, type your password into both Password box and Confirm box, and click the Ok button. Then you will see all selected cells are encrypted at once as following screen shot shown: Note: You can decrypt these encrypted cells with clicking Enterprise Decrypt, and then type you password into opening dialog box. Includes more than 300 handy Excel tools. Full feature free trial 60-day, no credit card required!
Demo: lock and protect selected cells in Excel. Increase your productivity in 5 minutes.
Don't need any special skills, save two hours every day! 300 New Features for Excel, Make Excel Much Easy and Powerful:. Merge Cell/Rows/Columns without Losing Data. Combine and Consolidate Multiple Sheets and Workbooks. Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion. Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,.
More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools.