The error is caused by the user interface for Excel trying to modify the spreadsheet at the same time as Visual Basic code programs. This applies to any Visual Basic program but it particularly applies to event driven programming, for example Timers or ActiveX events.
This error can also happen when using CX-Server Lite, for example if you are subscribing to a PLC point using the 'GetData' function and writing the result of this data direct to a Cell on the spreadsheet from the 'On Data' callback. If you are editing or clicking on the spreadsheet at the same time as the OnData event occurs, this error situation will occur. The solution is to trap the problem using 'On Error'
For Example:
Private Sub Comms1_OnData(ByVal PLC As String, ByVal Point As String, ByVal Value As Variant, ByVal BadQuality As Boolean)
On Error GoTo errhandler
Sheet1.Cells(5, 5) = Value
Exit Sub
errhandler:
' We couldn't write to the cell, probably due to it being used by Excel itself
' We will ignore this
End Sub
You can see here that we are writing the value to Cell 5,5. If there is a problem, we ignore it and wait for the next update.
The risk here is that we do fail to update the sheet when this issue occurs, but we trap any error message. Instead of the comment in the error handler, you could add some extra error handling or a suitable error message to suit you.
You can reduce the chance of this problem occurring by locking most of the cells that you wouldn't want to edit and choosing to Protect the spreadsheet, so random clicks of the mouse will not cause the issue as it doesn't open 'edit mode'. There is still a chance this will occur when you click on 'Editable' cells.
This program will now work and no error message will be shown if the cell cannot be updated by the OnData function.
