INFO: Setting ReturnValue to False Does Not Cancel Spreadsheet Event

ID: Q240902

The information in this article applies to:

Microsoft Office Spreadsheet Component 9.0, version 1.0

SUMMARY

The SpreadSheet Web component supports events. Event procedures have a parameter for a SpreadsheetEventInfo object; this object contains information about the event. The documentation states that you can "cancel" some events by setting the ReturnValue of the SpreadsheetEventInfo object to False. This article describes which events can and cannot be canceled by setting ReturnValue to False.

MORE INFORMATION

The following events of the SpreadSheet Web component can be canceled by setting ReturnValue to False:

BeforeCommand
StartEdit
EndEdit

The following events cannot be canceled by setting ReturnValue to False:

Calculate **
CancelEdit
Change **
Click
Command
DblClick **
KeyDown
KeyPress
KeyUp
MouseDown
MouseOut
MouseOver
MouseUp
SelectionChange
SelectionChanging
ViewChanging **

** See the following section for more details.

Notes

Some events that are not "cancelable" through ReturnValue = False may be canceled using another approach.

Preventing Calculation

The Calculate event is triggered when you press F9 in the Spreadsheet component. The Calculate event itself cannot be canceled by setting ReturnValue to False, but it can be canceled through the BeforeCommand event. The BeforeCommand event is fired when a variety of commands are executed. You can examine the Command property of the SpreadsheetEventInfo object to detect a specific command.

Use code similar to the following to cancel a user request to recalculate in the SpreadSheet component's BeforeCommand event:


Private Sub Spreadsheet1_BeforeCommand(ByVal EventInfo As 
                                       OWC.SpreadsheetEventInfo)
    If EventInfo.Command = Spreadsheet1.Constants.ssCalculate Then
        EventInfo.ReturnValue = False
    End If
End Sub

The BeforeCommand event does not trap automatic calculations; an automatic calculation occurs when a cell contains a formula that references a cell that has changed. To prevent automatic calculations, set the EnableAutoCalculate property of the Spreadsheet component to False.

Preventing Changes to Cells

The Change event is fired whenever data in one or more cells changes due to edits or copy-and-paste operations. To prevent data in one or more cells from getting changed, you can cancel the StartEdit event and cancel the BeforeCommand event when you detect a Paste, Cut, or Delete command:


Private Sub Spreadsheet1_BeforeCommand(ByVal EventInfo As 
                                       OWC.SpreadsheetEventInfo)
    'Don't let any thing change b6
    Set X = Spreadsheet1.Union(Spreadsheet1.Selection, 
                               Spreadsheet1.Range("b6"))
    If Not (X.Cells.Count > Spreadsheet1.Selection.Cells.Count) Then
        If EventInfo.Command = _
                ssPaste Or ssDeleteRows Or ssDeleteColumns Or ssCut Then
            MsgBox "Cell B6 cannot be changed."
            EventInfo.ReturnValue = False
        End If
    End If
End Sub

Private Sub Spreadsheet1_StartEdit(ByVal EventInfo As 
                                   OWC.SpreadsheetEventInfo)
    If EventInfo.Range.Address = "B6" Then
        MsgBox "Cell B6 cannot be changed"
        EventInfo.ReturnValue = False
    End If
End Sub

Note that this sample code does not trap for commands that might shift the contents of B6 to another cell. For example, if you inserted a row at row 6, the contents of B6 would move to B7. You can code for these types of considerations using the BeforeCommand event as well.

Canceling a Double Click

The DblClick event of the SpreadSheet component essentially places the user in Edit mode. To disallow edits, you can cancel the StartEdit event.

Preventing Changes to the View

You cannot cancel the ViewChanging event with ReturnValue. However, you can prevent your users from changing the view for the Spreadsheet Web component by setting the AllowPropertyToolbox property to False. This property can be set at run time or at design time in the Spreadsheet component's property sheet.

Additional query words: owc worksheet

Keywords : kbDSupport kbOfficeWebSpread
Version : WINDOWS:1.0
Platform : WINDOWS
Issue type : kbinfo