Exception in Spread 5

Posted by: tosch on 8 September 2017, 2:25 pm EST

  • Posted 8 September 2017, 2:25 pm EST

    In my application pressing Enter in a cell has to move to the next cell. I have implemented this in a class to be used in different locations. This code runs fine in Spread 3.x but crashes in Spread 5.

    Below is the code I use to achieve this.I have stripped out a lot of other custom code. This is just the essentials you need to reproduce the problem when pressing Enter in a combo cell. It works fine in all other celltypes (code left out).

    Please tell me if I'm doing something wrong or if this is a spread bug.

     

        Private bPressedEnterKey As Boolean
        Private m_movetonextrow As Boolean

        Private Sub FpSpread_EditModeOn(ByVal sender As Object, ByVal e As System.EventArgs) Handles FpSpread1.EditModeOn
            Dim bBeforeSpreadEdit As Boolean = True

            If TypeOf FpSpread1.EditingControl Is FarPoint.Win.FpCombo Then
                CType(FpSpread1.EditingControl, FarPoint.Win.FpCombo).AcceptsReturn = True
                AddHandler FpSpread1.EditingControl.KeyDown, AddressOf Combo_EditKeyDown
            End If
        End Sub

        Private Sub Combo_EditKeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs)
            If e.KeyCode = System.Windows.Forms.Keys.Enter Then
                bPressedEnterKey = True
                FpSpread1.EditMode = False
            End If
        End Sub

        Private Sub FpSpread_EditModeOff(ByVal sender As Object, ByVal e As System.EventArgs) Handles FpSpread1.EditModeOff
            Try
                If TypeOf FpSpread1.EditingControl Is FarPoint.Win.FpCombo Then
                    RemoveHandler FpSpread1.EditingControl.KeyDown, AddressOf Combo_EditKeyDown
                End If
                AfterSpreadEdit()
                bPressedEnterKey = False
            Catch
            End Try
        End Sub

        Private Sub AfterSpreadEdit()
            Dim intRow As Integer = FpSpread1.ActiveSheet.ActiveRowIndex
            Dim intCol As Integer = FpSpread1.ActiveSheet.ActiveColumnIndex
            Dim fpsheet As SheetView = FpSpread1.ActiveSheet

            If bPressedEnterKey Then
                'Move next cell

                If Not m_movetonextrow Then
                    Do
                        If intCol + 1 > fpsheet.ColumnCount - 1 Then
                            intCol = 0
                            If intRow + 1 > fpsheet.RowCount - 1 Then
                                intRow = 0
                            Else
                                intRow += 1
                            End If
                            'Exit Do
                        Else
                            intCol += 1
                        End If
                    Loop Until fpsheet.Columns(intCol).Visible AndAlso Not fpsheet.Columns(intCol).Locked
                Else
                    Do
                        If intRow + 1 > fpsheet.RowCount - 1 Then
                            intRow = 0
                            If intCol + 1 > fpsheet.ColumnCount - 1 Then
                                intCol = 0
                            Else
                                intCol += 1
                            End If
                        Else
                            intRow += 1
                        End If
                    Loop Until fpsheet.Rows(intRow).Visible AndAlso fpsheet.Columns(intCol).Visible 'AndAlso Not FpSheet.Rows(intRow).Locked
                End If

                SetActiveCell(intRow, intCol)
                bPressedEnterKey = False
            End If
        End Sub

        Public Sub SetActiveCell(ByVal intRow As Integer, ByVal intCol As Integer)
            Dim defselModel As New FarPoint.Win.Spread.Model.DefaultSheetSelectionModel()
            defselModel.SelectionPolicy = Model.SelectionPolicy.MultiRange
            FpSpread1.ActiveSheet.Models.Selection = defselModel
            defselModel.SetSelection(intRow, intCol, 1, 1)
            FpSpread1.ActiveSheet.SetActiveCell(intRow, intCol)
            FpSpread1.ActiveSheet.ClearSelection()
        End Sub
     

  • Replied 8 September 2017, 2:25 pm EST

    Hello Tosch,

    Unfortunately i am unable to replicate the issue at my end using the code you have provided us with and using the latest version of Spread 5, wherein on pressing enter in comboboxcelltype the focus remains in combobox cell only and the cell goes in edit mode.

    However, as per the problem statement "pressing Enter in a cell has to move to the next cell", you can use the code given below using InputMap wherein SpreadActions is set to MoveToNextColumn.

    Please have a look at the code:-

            Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

            Dim cmbocell As New FarPoint.Win.Spread.CellType.ComboBoxCellType()

            cmbocell.Items = (New String() {"January", "February", "March", "April", "May", "June"})

            cmbocell.AcceptsArrowKeys = FarPoint.Win.SuperEdit.AcceptsArrowKeys.AllArrows

            cmbocell.AutoSearch = FarPoint.Win.AutoSearch.SingleCharacter

            cmbocell.Editable = True

            cmbocell.ListAlignment = FarPoint.Win.ListAlignment.Left

            cmbocell.ListOffset = 20

            cmbocell.ListWidth = 0

            cmbocell.MaxDrop = 4

            FpSpread1.Sheets(0).Cells(2, 2).CellType = cmbocell

     
            Dim im As New FarPoint.Win.Spread.InputMap

            ' Define the operation of pressing Enter key in cells not being edited as "Move to the next column". 

            im = FpSpread1.GetInputMap(FarPoint.Win.Spread.InputMapMode.WhenFocused)

            im.Put(New FarPoint.Win.Spread.Keystroke(Keys.Enter, Keys.None), FarPoint.Win.Spread.SpreadActions.MoveToNextColumn)

            ' Define the operation of pressing Enter key in cells being edited as "Move to the next column".

            im = FpSpread1.GetInputMap(FarPoint.Win.Spread.InputMapMode.WhenAncestorOfFocused)

            im.Put(New FarPoint.Win.Spread.Keystroke(Keys.Enter, Keys.None), FarPoint.Win.Spread.SpreadActions.MoveToNextColumn)

           End Sub

    Hope this will help you. Else you can send a running sample for us to debug at our end. Thanks.

     

  • Replied 8 September 2017, 2:25 pm EST

    Thanks for your answer, I will try the code you suggested.

    I have also attached a sample app to demonstrate the problem (VS2010).

    To recreate the problem select any entry in the combobox using the mouse or the keyboard. Then press enter and you should get an error message: object not set to an instance of an object.


    2010/08/Spread5_ComboEnter.zip
  • Replied 8 September 2017, 2:25 pm EST

    Hello Tosch,

    I am able to replicate the issue at my end using the .exe in your attached sample. However, I tried creating the same into another project using the above mentioned code and I am not able to replicate the issue using the above code. However, on opening the form i am getting an error which states that the form1.vb doesn't exist in the current directory. Can you please send sample again and make sure that the form1.vb exist in order to debug the issue further at my end.

    Thanks.

  • Replied 8 September 2017, 2:25 pm EST

    I didn't realise one file was missing, sorry about that. I have attached the source code again.

     


    2010/08/Spread5_ComboEnter-2.zip
  • Replied 8 September 2017, 2:25 pm EST

    Hello Tosch,

    We are able to replicate the issue at my end wherein it is throwing an exception when the editable property of the combobox is set to False. However, we have reported it as a bug in Spread version 5.0. and bug number for the same is #99917686. It will be fixed in the future maintenance release of Spread.

    And by that time you can try using the above mentioned workaround i suggested. Hope it will help you. Thanks.

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels