Function Sequence Error that resolves if I go into design mode and back out, but comes back everytime I open this Calc document

English
, ,
#1

I cannot figure out why every time I open my calc document, this code throws a Function Sequence Error when called. Then the error resolves if I switch to design mode and back, and stays resolved unless i quit and reopen the document. But I need this to work without doing that so other people can use it!

The form in question is a reference to a single line of a table, it exists, is modifiable. It is defined and called and loaded before the line that throws the error. Pasting the code below, with a note on the line that throws the error.

Edited to say: It is assigned to Sheet Event > Activate Document, so it is called when I navigate to this sheet, but tested on other event as suggested, and still gets the error.

Sub LoadContactPage

Dim oSheet, oForm, oListB, oSubform As Object, nID As Integer
oSheet = ThisComponent.Sheets.getByName(“Contact”)
oForm = oSheet.getDrawpage().getForms().getByName(“Form”) ’ table tLookup, Key=2
oSubform = oForm.getByName(“Info”) ’ table tMember, linked by ID
oForm.reload()
oListB = oForm.getByName(“LBGroup”)
oListB.refresh()
oForm.reload()
nGrp = 0
sName = “”
nID = oForm.getInt(4) 'why does this get caught up on load??? Function sequence error???
If nID > 0 Then
nGrp = oSubform.getInt(4)
End If
sGroup = "Home Group: " & GetGrpWTwn(nGrp)
'show HG
oSheet.getCellByPosition(3, 6).setString(sGroup)

'set selected cell
ThisComponent.CurrentController.select( oSheet.getCellByPosition(0, 0) )
End Sub

#8

Please upload your file containing the specified macro. Before uploading, you can delete confidential data and anything irrelevant to the issue.

#9

Thank you for the reply, it will take some time for me to do this, it’s attached to a database that is confidential.

#10

I wasn’t able to strip the file of confidential info, so I gave up and found a macro way of toggling design mode, so that seems to be my fix until/unless I ever find the root of the issue. Toggle Desig Mode in Macro; No Dispatch!

I’ve been running in circles revising the macros, or switching the activation events, or calling them in different manners from the buttons that trigger them. And everytime I believe it’s fixed, it proves to be only temporary. Weirdly sometimes lasting through rebooting the program, but starting again a few reboots following.

#2

The appropriate event to use should be View created, imo.

#3

Why? That’s a document wide event, I need this to run when a specific sheet is made the active sheet.

#4

Why why?
(I’m not the guru, but…)
Activate document is the event thrown when the document is activated. (View created should follow a few ms later.)
There isn’t an event like Activate sheet. Your case needs to be treated under Selection changed.
This isn’t exactly simple (and even a bit buggy), imo.

I don’t use self-made listeners, but you may get something from subchapter 14.4 of Andrew Pitonyak’s “Useful Macro Information”.

See also Calc Sheet Activate event? - #3 by jfn.

#5

That thread suggests sheet event Activate Document is effectively Activate sheet, which is true in my experience. Regardless, I tried your suggestions just for kicks and it doesn’t help solve the problem.

#6

As I already told you, I don’t use this kind of listeners. However, @jfn and myself both tell

Ipointed you to the accepted solution by @jfn, not to a comment by @vib.

  • no there’s currently no such event as Sheet change that could be specified in the document events. You’ll have to set a listener for that.

What comes next to Sheet changed is Selection changed. There are lots of complications. If you found an error in the quoted post and code, you should post to that question, and probably you get a useful answer by the Qustioner who accepted the answer as solution or by @vib who wrote the code.

How so? Your code was based on that assumption - and failed.

(I’m out now.)

#7

You’re quoting the person in the thread (@ vib) who was then told they are wrong. Keep reading. And the sheet change comments don’t apply because that is their problem not mine. And my code isn’t based on that assumption, and your suggestion didn’t change anything, I did try it. Peace out.

#11

Therefore: it is connected to the database. So the problem is timing.

Solution proposal:

Sub LoadContactPage
    Dim oSheet As Object, oForm As Object, oSubform As Object, oListB As Object
    Dim nID As Integer, nGrp As Integer, sGroup As String

    ' Access sheet and form controls
    oSheet = ThisComponent.Sheets.getByName("Contact")
    oForm = oSheet.getDrawPage().getForms().getByName("Form") ' table tLookup, Key=2
    oSubform = oForm.getByName("Info") ' table tMember, linked by ID
    oListB = oForm.getByName("LBGroup")

    ' Refresh form and listbox
    oForm.reload()
    oListB.refresh()

    ' Wait for form to be ready before accessing fields
    If Not WaitForFormReady(oForm) Then
        MsgBox "Form not ready. Try again later.", 48, "Load Error"
        Exit Sub
    End If

    ' Try to get ID safely
    On Error GoTo RetryGetID
    nID = oForm.getInt(4)
    GoTo ContinueID

RetryGetID:
    Wait 200
    On Error GoTo FailGetID
    nID = oForm.getInt(4)

ContinueID:
    If nID > 0 Then
        nGrp = oSubform.getInt(4)
    Else
        nGrp = 0
    End If

    sGroup = "Home Group: " & GetGrpWTwn(nGrp)

    ' Show HG
    oSheet.getCellByPosition(3, 6).setString(sGroup)

    ' Set selected cell
    ThisComponent.CurrentController.select(oSheet.getCellByPosition(0, 0))
    Exit Sub

FailGetID:
    MsgBox "Unable to retrieve ID from form.", 48, "Data Error"
End Sub

Function WaitForFormReady(oForm As Object) As Boolean
    Dim i As Integer
    For i = 0 To 20 ' wait up to ~2 seconds
        If oForm.RowCount > 0 And oForm.isActive() Then
            WaitForFormReady = True
            Exit Function
        End If
        Wait 100
    Next i
    WaitForFormReady = False
End Function
1 Like
#12

No warranty, so don’t blame me if it doesn’t work :smirk:

1 Like
#13

Thank you so much! I only just learned to use On Error last week and your idea really helps me level up to what this may need. Plus the waitforformready sounds like exactly the proble. I’m excited to try it out later today. I have a good feeling this is going to solve my problem.

#14

@Pertsa I get an error saying oForm.isActive() is an unrecognized. Do you know if I missing a certain library for that? Google’s AI result is suggesting that its not a standard protocol, but also that it exists, and the links that come up aren’t giving me any details.

If not, or in the meantime, I modified it to toggle DM on RetryGetID which is more direct than what I had been doing and may provide a seamless user experience. I guess we’ll see with some more testing. Thank you for your help.

#15

Forget Google AI!
There is no such a function as oForm.isActive()

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.