[Solved] Forms: Bug or Feature?

Discuss the spreadsheet application
Post Reply
AndresSolar
Posts: 72
Joined: Sun Jun 26, 2016 5:42 am

[Solved] Forms: Bug or Feature?

Post by AndresSolar »

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
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Forms: Bug or Feature?

Post by Villeroy »

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Zizi64
Volunteer
Posts: 11345
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Forms: Bug or Feature?

Post by Zizi64 »

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; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
AndresSolar
Posts: 72
Joined: Sun Jun 26, 2016 5:42 am

Re: Forms: Bug or Feature?

Post by AndresSolar »

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
User avatar
Zizi64
Volunteer
Posts: 11345
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Forms: Bug or Feature?

Post by Zizi64 »

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; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
AndresSolar
Posts: 72
Joined: Sun Jun 26, 2016 5:42 am

Re: Forms: Bug or Feature?

Post by AndresSolar »

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: 72
Joined: Sun Jun 26, 2016 5:42 am

Re: Forms: Bug or Feature?

Post by AndresSolar »

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

'___________________________________________________________________________________________________________
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
User avatar
robleyd
Moderator
Posts: 5037
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Forms: Bug or Feature?

Post by robleyd »

[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
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
AndresSolar
Posts: 72
Joined: Sun Jun 26, 2016 5:42 am

Re: Forms: Bug or Feature?

Post by AndresSolar »

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: 72
Joined: Sun Jun 26, 2016 5:42 am

Re: Forms: Bug or Feature?

Post by AndresSolar »

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

Re: Forms: Bug or Feature?

Post by AndresSolar »

@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
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Forms: Bug or Feature?

Post by Villeroy »

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
AndresSolar
Posts: 72
Joined: Sun Jun 26, 2016 5:42 am

Re: Forms: Bug or Feature?

Post by AndresSolar »

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

Re: [Solved] Forms: Bug or Feature?

Post by AndresSolar »

...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
Post Reply