About VBA-UserForms

[Pertsa]

Thanks, but unfortunately the link you provided is dead. I am aware of how the event listener can be built and so on. That’s not the problem, but how to get handle to the controls which are invisible in designer. And foremost, is the tabs object defined in some of UNO service so it can find possible child controls placed on a tabs object. If I can get the handle to these controls I can use a structure (type) and give them properties and then collect all into an array and so on…

Here’s an example of the codebehind my VBA UserForm.

[Villeroy]

No, there are no tabs in StarBasic. StarBasic dialogs support “steps”. Steps control the visibility of selected controls. Controls with Step=0 are always visible. Controls with Step=1 are visible when the dialog has Step=1 and so on.
StepDialogDemo.ods (20.0 KB)

[Pertsa]

So there’s no easy way around this. It seems that I need to place the controls in four group boxes and set a button for each one so that the associated group box appears while the others will be hidden. Thanks anyway.

[Villeroy]

You have to rebuild the entire VBA application. VBA is the ring to bind them all, forever and ever.

[Pertsa]

That’s how I understood it.

[Pertsa]

@Villeroy
You clearly have clairvoyant gifts!

Damned, I should have checked first what is going on nowadays…
The latest API (07.02.2025) contains so many changes compared to API (07.07.2009) which I used in my old VBA-tuning that I have to code everything again from the beginning or forget the whole thing.

Here’s the result

[Pertsa]

I couldn’t give it up and this is the result:

Sub ListDialogControls
	
	Dim myMultiPage As Variant
	Dim myMultiPageControl As Variant
	
	Dim i As Integer, j As Integer, k As Integer, ctlName As String
	
	For i = 0 To uBound(MyForm1.getControls())
		If MyForm1.getControls()(i).Model.Name = "MultiPage1" Then
			myMultiPage = MyForm1.getControls()(i)
			For j = 0 to uBound(myMultiPage.getControls())
				ctlName = myMultiPage.getControls()(j).Model.Name
				If myMultiPage.getControls()(j).Model.Name = ctlName Then
					myMultiPageControl = myMultiPage.getControls()(j)
					For k =  0 To Ubound(myMultiPageControl.getControls())
						MsgBox myMultiPageControl.getControls()(k).Model.Name
					Next k
				End If
			Next j
		End If
	Next i
	
End Sub

[Pertsa]

Also the property values ​​set for the controls still exist, they can be found and, if desired, set new values. The code can be found here

[Villeroy]

I would be grateful, if you could post a sample document with that code. I did not touch Excel in 23 years. Microsoft products (other than their darned operating system) are not allowed on our computers. Therefore, I have no objects your code could relate to, and LO can’t create those objects.

[Pertsa]

Which do you want, the native VBA or the LibreOffice Basic tuning I’m working on now, or maybe both?

[Villeroy]

@Pertsa

Which do you want, the native VBA or the LibreOffice Basic tuning I’m working on now, or maybe both?

Anything that makes variable myMultiPageControl inspectable in LibreOffice.

[Pertsa]

MHLO.ods (18.7 KB)

[sokol92]

@Pertsa

But I haven’t found a way to create an event listener for the controls which are not visible on design mode

An example of dynamically adding listeners for dialog controls is in the famous book by Andrew Pitonyak OOME_4_1.odt, listing “Create the dialog, the controls, and the listeners.”

[Villeroy]

@sokol92

An example of dynamically adding listeners for dialog controls is in the famous book by Andrew Pitonyak OOME_4_1.odt, listing “Create the dialog, the controls, and the listeners.”

As far as I understand the matter, the sample document MHLO.ods includes a multi-page dialog generated by MSO which can not be generated with LibreOffice. I did not know, that LO can show the multi-page dialog anyway.

[sokol92]

@Villeroy

the sample document MHLO.ods includes a multi-page dialog generated by MSO which can not be generated with LibreOffice

The com.sun.star.awt.UnoControlMultiPage and com.sun.star.awt.UnoControlPage services are not documented and therefore should not be used in any case.

[Pertsa]

@sokol92:

Thanks, but I already found this document while digging online.

[Pertsa]

@sokol92:

I have no idea what com.sun.star - services or whatsoever the Basic Designer uses. I simply exported couple of UserForms from my old Excel/VBA -project and I think the MultiPage control looks good when it’s visible. I myself don’t give a much about what some documentation says or not say, but I like this control object and the challange it causes.

[sokol92]

@Pertsa

I think the MultiPage control looks good when it’s visible

This means that the LibreOffice developers have done a good job of converting Userform object descriptions into LibreOffice objects.
My response to @Villeroy 's post was about context:

@Villeroy

which can not be generated with LibreOffice

[Pertsa]

Btw, I came across another challenge. I have some picture data stored to a .mdb file as bytes and some other information stored as plain text. I couldn’t get the ms jet engine provider working. I can create a connection object but Basic didn’t accept any of connection strings I’ve tried. Then I downloaded UCanAccess-5.0.1 and added all the .jar files to class path, but with no success. I couldn’t even create the connection object. I got mad and decided work around this by using WScript Shell. The downside is that I have to extract the data from the database to files and then load the files so I can get access to them. I think I’ll take the WMI for help and read output directly from the Shell, it doesn’t matter if the data is visible or not.

[Villeroy]

Stop coding VBA. Start using the application.
menu:File>New>Database…
Connect to existing db
Type: MS Access
Point to the file
Database registration makes the data available to office documents.
Save the database document.

Now you may use the MRI or XRay extension to analyse the connection from a coder’s view point. However, Base connections do not ask for macro coding in the first place.