How to Select Locked Cells in Excel VBA Without Allowing Edits
Question details
The user needs to allow selection of locked cells on a protected worksheet to trigger a right-click macro event without letting users edit the content.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Protecting a worksheet where users enter search criteria and need to click on resulting locked cells to trigger a Worksheet_BeforeRightClick macro.
- Observed behavior
- Applying the EnableSelection property to a Range object results in Run-time error 438 because EnableSelection is a Worksheet property, not a Range property.
Ensure you have the Developer tab enabled in Excel and know the password for your protected worksheet before modifying the VBA code.
Apply the EnableSelection Property to the Worksheet Object
Fix Run-time error 438 by correctly applying the EnableSelection property to the Worksheet object rather than a specific Range.
The EnableSelection property belongs exclusively to the Worksheet object. Attempting to use a syntax like `rng.EnableSelection` applies the property to a Range object, which Excel does not support, resulting in Run-time error 438.
By setting the worksheet's EnableSelection property to xlNoRestrictions, users can click on locked cells. This allows events like Worksheet_BeforeRightClick to fire while worksheet protection successfully prevents any modifications to the cell data.
Open your Excel workbook and press ALT + F11 on your keyboard to launch the Visual Basic for Applications (VBA) Editor.
In the Project Explorer pane on the left, double-click the module or worksheet where your current protection script is written.
Remove any lines attempting to apply EnableSelection to a Range (e.g., rng.EnableSelection = xlNoRestrictions).
Reference the worksheet object directly. Insert the following code block: ws.Unprotect ws.Protect ws.EnableSelection = xlNoRestrictions
Save your code and run the macro. Your worksheet will now be protected, but users will be able to select locked cells to trigger right-click events.
Execute VBA Macros Seamlessly in WPS Spreadsheet
WPS Office provides robust support for VBA macros, allowing you to run your existing Excel scripts, including worksheet protection and EnableSelection properties, with zero required code modifications.
- 1. Install WPS Office: Download and install WPS Office, then open your macro-enabled workbook (.xlsm) using WPS Spreadsheet.
- 2. Access the Developer Tools: Navigate to the 'Developer' tab on the top ribbon menu.
- 3. Open the VBA Editor: Click the 'Visual Basic' icon to launch the built-in VBA editor.
- 4. Run Your Code: Paste your corrected worksheet protection code and press F5 to execute the script seamlessly.
Frequently Asked Questions
Why do I get Run-time error 438 when using EnableSelection in Excel VBA?
Run-time error 438 occurs when a property or method is applied to an unsupported object. The EnableSelection property belongs to the Worksheet object. If you try to apply it to a Range object (e.g., Range("A1").EnableSelection), Excel will return this error.
Can I trigger a right-click event on a locked cell in Excel?
Yes. By protecting the worksheet and setting the worksheet's EnableSelection property to xlNoRestrictions via VBA, users can right-click locked cells to trigger the Worksheet_BeforeRightClick event without being able to edit the underlying cell values.
How do I restrict selection to only unlocked cells using VBA?
To prevent users from clicking on locked cells entirely, you must set the worksheet's EnableSelection property to xlUnlockedCells immediately after applying worksheet protection in your VBA script.
