Suppress Error on Modifying a Protected Cell

[Chris]

I have a worksheet with a lot of protected cells that cannot be edited. I want the cells to be protected but I don't need a warning to popup everytime the user tries to edit a protected cell. I would prefer to not have any warning display and just have the action silently fail.

Is there anyway to suppress the error dialog when the user tries to modify one of these protected cells?

[Nikolay Demirev]

Hi Chris,

You could attach a handler to the RadSpreadsheet.MessageShowing event and use the IsHandled property of its event args to cancel showing the warning:

this.radSpreadsheet.MessageShowing += this.RadSpreadsheet_MessageShowing;

private void RadSpreadsheet_MessageShowing(object sender, MessageShowingEventArgs e)
{
    e.IsHandled = true;
}

Regards,
Nikolay Demirev
Progress Telerik

[Chris]

Thank you for the reply.

This did work for me, though for others that may stumble upon this I slightly modified the code as the solution would suppress all messages, whereas I wanted to suppress only protected worksheet error messages.

Note that this doesn't appear to work for Pasting into a protected cell, since that still comes under a Paste Error.

this.radSpreadsheet.MessageShowing += this.RadSpreadsheet_MessageShowing;

private void RadSpreadsheet_MessageShowing(object sender, MessageShowingEventArgs e)
{
       if (e.NotificationType == MessageBoxNotificationType.ProtectedWorksheetError)
       {
           e.IsHandled = true;
       }
}

[Chris]

After further investigation I have noticed that this will cause an exception to occur if the user attempts to modify a protected cell using the Formula Bar. For some reason the absence of the the Error Message allows the user to be able to enter items into the bar for protected cells, which then causes and Exception from inside one of the Telerik modules.

Is there anyway to prevent this exception from occurring while still preventing the error message from displaying?

 

[Nikolay Demirev]

Hello Chris,

You are right, the case for pasting is different. I can suggest you change the paste commands in order to handle it. You will have to implement a custom paste command and use it in your context menu, if you are using one, and replace the command which is executed with the keyboard shortcuts. Here is a sample paste command implementation:

var pasteCommand = new RadSheetEditorDelegateCommand<RadWorksheetEditor>(
    this.radSpreadsheet.ActiveWorksheetEditor, (editor, parameter) =>
{
    PasteOptions pasteOptions;
    if (parameter != null)
    {
        PasteType pasteType = (PasteType)Enum.Parse(typeof(PasteType), Convert.ToString(parameter), true);
        pasteOptions = new PasteOptions(pasteType);
    }
    else
    {
        pasteOptions = PasteOptions.All;
    }

    bool isProtected = editor.Worksheet.IsProtected;
    if (isProtected)
    {
        RangePropertyValue<bool> lockedValue = editor.Selection.Cells.GetIsLocked();
        if (lockedValue.IsIndeterminate || lockedValue.Value)
        {
            return;
        }
    }

    editor.Paste(pasteOptions);
});

After implementing the command you need to change the context menu commands in XAML to use this command. Here is a blog post explaining how to customize the context menu https://www.telerik.com/blogs/radspreadsheet-tips-and-tricks-customize-the-context-menu-like-a-pro.

You also need to update the key bindings, so the Ctrl + V and Shift + Insert shortcuts execute the same command:

var keyBindings = this.radSpreadsheet.ActiveWorksheetEditor.KeyBindings;

keyBindings.RegisterCommand(pasteCommand, Key.V, ModifierKeys.Control);
keyBindings.RegisterCommand(pasteCommand, Key.Insert, ModifierKeys.Shift);

As for the exception in the Formula Bar, I have fixed it, it will be included in the next minor release here is a public item in our Feedback portal you can track its progress there. The fix only prevents exceptions, but still, a value can be entered in the TextBox, but it will not be applied to the cells.

Regards,
Nikolay Demirev
Progress Telerik

[Chris]

Thanks for the update. I have to admit I was just checking the content of the event string to see if the paste error contained the word "protected" and setting it to handled but am sure your approach is the correct way to handle this situation.

Also thank you for the confirmation of the exception issue. For now we have just disabled the bar completely as its not really needed.