[Solved] Forms: Bug or Feature?

Discuss the spreadsheet application

[Solved] Forms: Bug or Feature?

Postby AndresSolar » Tue Apr 09, 2019 8:28 pm

Hello together,

In a document with 2 sheets I have 5 comboboxes and 80 image-buttons, macros for right-click menue (all navigation stuff) on the latest LO release 6.2.3.1

Drawpage count shows approx. 3600 elements.

The ones I created and thousands of identical TextBoxes named "Push Button 2", which can't be deleted - neither using the Form navigator - nor during runtime via macro.

Since the buttons are used to navigate a very complex sheet (by hiding/unhiding Row-Ranges) I have to loop through that garbage elements all the time (...to find the anchor-cell of my buttons). With every simple image-button I add, the number of those "parasitic" elements increases too.

Is this a bug or am i missing something?

How to get rid of those elements?

Is there a way to get the anchor cell of a button (not using the button event) without blindly looping through all elements and checking for a certain parameter?

Many thanks in advance
Last edited by Hagar Delest on Wed Apr 17, 2019 7:51 am, edited 1 time in total.
Reason: tagged solved
LO6.2 on OS-X Mojave 10.14.3
AndresSolar
 
Posts: 68
Joined: Sun Jun 26, 2016 5:42 am

Re: Forms: Bug or Feature?

Postby Villeroy » Tue Apr 09, 2019 9:01 pm

Forms belong to databases and this is what you should use instead of a calculator.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27114
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Forms: Bug or Feature?

Postby Zizi64 » Tue Apr 09, 2019 9:07 pm

Drawpage count shows approx. 3600 elements.

The ones I created and thousands of identical TextBoxes named "Push Button 2", which can't be deleted - neither using the Form navigator - nor during runtime via macro.


How you tried to get and delete them by your macro? Try to get them by index.
How they was created? Manually,or by Copy-paste or by macro?
How they was named? Why you named the Textboxes as "Push Button 2"? Never use identical names for different elements.

Can you share the file and the macros here?
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8228
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Forms: Bug or Feature?

Postby AndresSolar » Tue Apr 09, 2019 9:56 pm

Villeroy (I knew, you'd say that... :) - I see your point but I already use a database.

The simulator works offline for several reasons. It is an engineering development tool with certified structural calculation models and must not be accessed automatically. The buttons are used to (visually) navigate and control the very long calculation chains (from the solar system model all the way down to the battery cycle optimization).

While the simulator works just great on LO & OO (20 full days with realtime visualization -24 Sparkbars for 35 simulator elements & data handling = 1 sec.) all attempts with Excel failed mainly because of performance issues. LO is lightning fast).

Zizi: I use a loop found here checking for the button name "Push Button 2". It seems to work - (finds all the elements) and processes:

oForm.removeByName(sCtrlToDel)
oEvent.source.Context.removeControl(oCtrl)
oCtrl.dispose

...but... the elements are still there in the Form Navigator

I never created or named those elements. They popped up somewhen for no visible (for me) reason. Since the Form Navigator allows everything else (rename, change properties, add macros, etc.), it refuses to delete (not greyed out) or cut them.

I can't find them anywhere on the sheet although they are "visible" and they are all linked to the whole sheet range from $A$1 to the last position. The position and size properties show reasonable values. But nothing there even if i change the elements name, type, text, etc. Save, Close, Quit, Reload. Nothing helps
LO6.2 on OS-X Mojave 10.14.3
AndresSolar
 
Posts: 68
Joined: Sun Jun 26, 2016 5:42 am

Re: Forms: Bug or Feature?

Postby Zizi64 » Tue Apr 09, 2019 10:34 pm

I never created or named those elements. They popped up somewhen for no visible (for me) reason.

I can not say anything about it without seeing your sample file, and the full macro code.
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8228
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Forms: Bug or Feature?

Postby AndresSolar » Tue Apr 09, 2019 10:53 pm

Zizi: I'm stripping a copy until tomorrow (your time)

Thanks to both of you
LO6.2 on OS-X Mojave 10.14.3
AndresSolar
 
Posts: 68
Joined: Sun Jun 26, 2016 5:42 am

Re: Forms: Bug or Feature?

Postby AndresSolar » Wed Apr 10, 2019 11:36 pm

I found the mistake by checking the data sources. Most likely a combobox was connected to Bilbliography DB.
I have removed the data source, but the Form Navigator still shows me:

Forms
> Form
> Form

both filled up with Text Boxes, Name "Push Button 2"
I managed to delete some of them by changing the anchor and position to cell A1.
Select and delete works if they show up.

Since I have replaced all buttons, etc. with a routine which turns any cell I want to a "button",
it doesn't bother for now - although it would feel better to not have that garbage in the file.

The new buttons are fast, robust and as nice as desired :)
The button animation simulates a typical mouse-over, clicked, released cycle, so i didn't need a listener-sub.

Concept:
- Use Cell styles and a modified font for the animation of the Button instead of images, etc.
- create unique named ranges for each button-cell
- trigger the sub through sheet event "double click"
- Loop: intersect current selection with button names stored in array
-> ...do button stuff...

As soon as I understood how to upload a file I'll post it here.
I'll leave the sub's in the file.

Many thanks - again learned a lot!

Code: Select all   Expand viewCollapse view
'___________________________________________________________________________________________________________
option Explicit
Global  countE     As Long
Sub ButtonManager(oEvent)
   Dim x As Long
   Dim cDoc As Object  : cDoc   = ThisComponent
   Dim cSheet As Object  : cSheet = cDoc.CurrentController.ActiveSheet
   Dim nameCase As String
   Dim cFontName As String
   Dim xSection As String
   Dim iSection As String
   Dim scenario As String
   Dim tSection As Object
   'reset the mouse-click counter and exit, if it's just a double gaenger
   On Error GoTo ErrorHandler
   'register the Button-Senarios
   Dim cbScenario As Variant : cbScenario = Array("cbU_Open","cbU_Close","cbL_Open","cbL_Close","cbR_Open","cbR_Close")
   Dim namesButtonsSim As Variant
   namesButtonsSim = Array("cbU_TimeTransport",                      "cbL_TimeTransport",      "cbR_TimeTransport",_
                     "cbU_StatusPG",         "cbL_StatusPG",            "cbR_StatusPG",_
                      "cbU_EnergyDemand1",      "cbL_EnergyDemand1",      "cbR_EnergyDemand1",_
                      "cbU_ProductionPV",         "cbL_ProductionPV",         "cbR_ProductionPV",_
                      "cbU_OvercapacityPV",      "cbL_OvercapacityPV",      "cbR_OvercapacityPV",_
                      "cbU_StatusPG",         "cbL_StatusPG",            "cbR_StatusPG",_
                      "cbU_EnergyDemand1",      "cbL_EnergyDemand1",      "cbR_EnergyDemand1",_
                      "cbU_RemainingDemand1",   "cbL_RemainingDemand1",      "cbR_RemainingDemand1",_
                      "cbU_ChargingRatePV",      "cbL_ChargingRatePV",      "cbR_ChargingRatePV",_
                      "cbU_ChargingLossesPV",      "cbL_ChargingLossesPV",      "cbR_ChargingLossesPV",_
                      "cbU_RemainingOvercapPV",   "cbL_RemainingOvercapPV",   "cbR_RemainingOvercapPV",_
                      "cbU_StateOfCharge1",      "cbL_StateOfCharge1",      "cbR_StateOfCharge1",_
                      "cbU_ChargingRatePG",      "cbL_ChargingRatePG",      "cbR_ChargingRatePG",_
                      "cbU_ChargingLossesPG",      "cbL_ChargingLossesPG",      "cbR_ChargingLossesPG",_
                      "cbU_BypassRatePG",         "cbL_BypassRatePG",         "cbR_BypassRatePG",_
                      "cbU_BypassLossesPG",      "cbL_BypassLossesPG",      "cbR_BypassLossesPG",_
                      "cbU_RemainingDemand2",      "cbL_RemainingDemand2",      "cbR_RemainingDemand2",_
                      "cbU_StateOfCharge2",      "cbL_StateOfCharge2",      "cbR_StateOfCharge2",_
                      "cbU_DischargeRateMax",      "cbL_DischargeRateMax",      "cbR_DischargeRateMax",_
                      "cbU_DischargeRateUPS",      "cbL_DischargeRateUPS",      "cbR_DischargeRateUPS",_
                      "cbU_DischargeLossesUPS",   "cbL_DischargeLossesUPS",           "cbR_DischargeLossesUPS",_
                      "cbU_DischargeRateB",      "cbL_DischargeRateB",      "cbR_DischargeRateB",_
                       "cbU_DischargeLossesB",      "cbL_DischargeLossesB",      "cbR_DischargeLossesB",_
                      "cbU_StateOfCharge3",      "cbL_StateOfCharge3",      "cbR_StateOfCharge3",_
                      "cbU_RemainingDemand3",   "cbL_RemainingDemand3",      "cbR_RemainingDemand3",_
                     "cbU_EnergySold",         "cbL_EnergySold",         "cbR_EnergySold",_
                     "cbU_EnergyPurchased",      "cbL_EnergyPurchased",      "cbR_EnergyPurchased",_
                     "cbU_SuppliedByGenset",      "cbL_SuppliedByGenset",      "cbR_SuppliedByGenset",_
                     "cbU_EnergyLost",         "cbL_EnergyLost",         "cbR_EnergyLost")

   'catch an error
   If isMissing(oEvent.CharFontName) Then Exit Sub
   cFontName = oEvent.CharFontName
   'check if we clicked on of our cell's
   If oEvent.supportsService("com.sun.star.sheet.SheetCell") And cFontName = "Wingdings" Then
'      oEvent.Column = 2 Or 3 Or 17 Then : Exit Sub : Else
      'if so, then loop through all cell-button names and test if one intersects
      'with the oEvent-address
      For x = LBound(namesButtonsSim()) To UBound(namesButtonsSim())
         tSection = cSheet.GetCellRangeByName(namesButtonsSim(x))
         If tSection.queryIntersection(oEvent.getRangeAddress()).getCount >= 1 Then
            'safe the iSection name in xSection and preterminate the loop
            xSection = namesButtonsSim(x)
            x = UBound(namesButtonsSim())
            'now that we know the range name we can construct the iSection range name
            'case A: cellButton upper left section corner (cbU_...)
            If InStr(xSection, "cbU_") Then
               iSection = join(split(xSection, "cbU_"), "i")
               tSection = cSheet.GetCellRangeByName(iSection)
               If tSection.Rows.IsVisible = False Then
                  scenario = cbScenario(0) : Else : scenario = cbScenario(1)
               End If
            End If
            'case B: cellButton lower left section corner (cbD_...)
            If InStr(xSection, "cbL_") > 0 Then : iSection = join(split(xSection, "cbL_"), "i")
               tSection = cSheet.GetCellRangeByName(iSection)
               If tSection.Rows.IsVisible = False Then
                  scenario = cbScenario(2) : Else : scenario = cbScenario(3)
               End If
            End If            
            'case B: cellButton upper right section corner (cbR_...)
            If InStr(xSection, "cbR_") > 0 Then : iSection = join(split(xSection, "cbR_"), "i")
               tSection = cSheet.GetCellRangeByName(iSection)
               If tSection.Rows.IsVisible = False Then
                  scenario = cbScenario(4) : Else : scenario = cbScenario(5)
               End If
            End If
            'revolve cb-Styles:
            'standard -> over cell -> clicked -> released -> over cell -> clicked -> standard
            'delay-val's to mimic the missing mouse events
            Dim delay1 As Long : delay1 = 550'msec
            Dim delay2 As Long : delay2 = 700'msec
            Dim delay3 As Long : delay3 = 500'msec
            Select Case scenario
               Case "cbU_Open"
                  oEvent.CellStyle = "MouseOverCellButtonTop"
                    wait delay1
                  tSection.Rows.IsVisible = True
                  'reset screen focus to iSection
                    wait delay2
                  oEvent.CellStyle = "MouseClickCellButtonTop"
                    wait delay3
                  oEvent.CellStyle = "MouseReleasedCellButtonTop"
               Case "cbU_Close"
                  oEvent.CellStyle = "MouseOverCellButtonTop"
                    wait delay1
                  tSection.Rows.IsVisible = False
                  'reset screen focus to SubSection
                    wait delay2
                  oEvent.CellStyle = "MouseClickCellButtonTop"
                    wait delay3
                  oEvent.CellStyle = "StandardCellButtonTop"
               Case "cbL_Open"
                  oEvent.CellStyle = "MouseOverCellButtonBottom"
                    wait delay1
                  tSection.Rows.IsVisible = True
                  'reset screen focus to iSection
                    wait delay2
                  oEvent.CellStyle = "MouseClickCellButtonBottom"
                    wait delay3 
                  oEvent.CellStyle = "MouseReleasedCellButtonBottom"
               Case "cbL_Close"
                  oEvent.CellStyle = "MouseOverCellButtonBottom"
                    wait delay1
                  tSection.Rows.IsVisible = False
                  'reset screen focus to SubSection
                    wait delay2
                  oEvent.CellStyle = "MouseClickCellButtonBottom"
                    wait delay3   
                  oEvent.CellStyle = "StandardCellButtonBottom"
               Case "cbR_Open"
                  oEvent.CellStyle = "MouseOverCellButtonRight"
                    wait delay1
                  tSection.Rows.IsVisible = True
                  'reset screen focus to iSection
                    wait delay2
                  oEvent.CellStyle = "MouseClickCellButtonRight"
                    wait delay3
                  oEvent.CellStyle = "MouseReleasedCellButtonRight"
               Case "cbR_Close"
                  oEvent.CellStyle = "MouseOverCellButtonRight"
                    wait delay1
                  tSection.Rows.IsVisible = False
                  'reset screen focus to SubSection
                    wait delay2
                  oEvent.CellStyle = "MouseClickCellButtonRight"
                    wait delay3     
                  oEvent.CellStyle = "StandardCellButtonRight"
            End Select
         End If
      Next x
   End If
   Exit Sub
  ErrorHandler:
    ' ... individual code for error handling
End Sub
LO6.2 on OS-X Mojave 10.14.3
AndresSolar
 
Posts: 68
Joined: Sun Jun 26, 2016 5:42 am

Re: Forms: Bug or Feature?

Postby robleyd » Thu Apr 11, 2019 1:35 am

[Forum] How to attach a document here Note maximum file size is 128K. If your file is larger, use a file sharing site such as Mediafire and post the link here. The link also contains information on how to anonymise your document if it contains confidential information.
Cheers
David
Apache OpenOffice Developer Build 4.2.0 9820 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2950
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Forms: Bug or Feature?

Postby AndresSolar » Thu Apr 11, 2019 3:48 am

robieyd: many thanks. the file is quite large. even stripped. I have to set up an account since I never use online storage.
LO6.2 on OS-X Mojave 10.14.3
AndresSolar
 
Posts: 68
Joined: Sun Jun 26, 2016 5:42 am

Re: Forms: Bug or Feature?

Postby AndresSolar » Thu Apr 11, 2019 4:43 am

...squeeced it down to 128kb. The shapes are still there.
Attachments
cropped_SmartGridOptimizer V002-7 08042019 AL.ods
(124.5 KiB) Downloaded 17 times
LO6.2 on OS-X Mojave 10.14.3
AndresSolar
 
Posts: 68
Joined: Sun Jun 26, 2016 5:42 am

Re: Forms: Bug or Feature?

Postby AndresSolar » Tue Apr 16, 2019 7:54 pm

@Zizi:
I removed all scripts from the file and tried to remove the forms by index.
This always always crashes LO. After deleting all buttons, comboboxes, etc. i ended up with > 5.000 Forms of same type and name, popping out of the nowhere. Could you take a look at those Forms? - Thx
LO6.2 on OS-X Mojave 10.14.3
AndresSolar
 
Posts: 68
Joined: Sun Jun 26, 2016 5:42 am

Re: Forms: Bug or Feature?

Postby Villeroy » Tue Apr 16, 2019 8:55 pm

How to get rid of the form controls:
Select All (Ctrl+A or click the grey space between row and column headers)
Delete contents, uncheck everything, check "Objects", OK
Now you can user the forms navigator and delete the 2 forms.

Repeat for every sheet.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27114
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Forms: Bug or Feature?

Postby AndresSolar » Tue Apr 16, 2019 9:33 pm

:) that solved it - many thanks, Villeroy!!!
LO6.2 on OS-X Mojave 10.14.3
AndresSolar
 
Posts: 68
Joined: Sun Jun 26, 2016 5:42 am

Re: [Solved] Forms: Bug or Feature?

Postby AndresSolar » Thu Apr 18, 2019 3:28 am

...I've uploaded a short clip showing the cell-buttons for those interested. works perfectly for my needs.

https://youtu.be/_aKIODmzxRM
LO6.2 on OS-X Mojave 10.14.3
AndresSolar
 
Posts: 68
Joined: Sun Jun 26, 2016 5:42 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 22 guests