Display cell data in a dialog list box

[Neil-B]

Hi All,

I have a spread sheet that I use for my bank statements, as part of that I need to input various expense codes,
these codes come from a MSQL query as I open the Calc doc and there on a separate sheet. (CODES)

What I would have liked to do is have a Dialog box come up with a list of these codes and there meanings when I need a reminder of what they are.

Now making a dialog box with a list box and button isn’t to difficult, populating it with data is a little more involved and that’s where I have hit my problems.

If I put a list box directly onto a sheet I can link the cell but not so with the Dialog boxes, (Unless I am missing something)

my original thought was to have a button on my statement sheet so when the mouse is pressed it display the dialog and when released it closes (Quick peek at it) but it appears that once the button is pressed it continually runs the macro until that X is pressed in the corner.

I don’t need to do anything with this data just display it, I cant hard code the list either because the list values change over time hence the MYSQL.

Or could I populate this from MSQL rather that the cell values?

Can anyone point me in the right direction Please?

Regards Neil

[PYS]

Hi

Another suggestion: you might not create a dialog and benefit directly from the expected effect (opening the list on one click with closing without further intervention).

This is how the sidebar works: when it is displayed but “folded”, you can open it by clicking anywhere on its border (other than on the Show button).

As soon as the cursor is moved over the sheet again, the sidebar folds back.

So you could simply create comments, either from cells in the CODE sheet as in the attached example, or directly from your database.

The program is very simple (create or delete), you don’t have to handle the display.

Sub Main

dim oSheet as object, oCell as object
dim i as long
oSheet = thiscomponent.sheets.getByName("Codes")
for i = 0 to 9
	oCell = oSheet.getCellByPosition(0, i)
	oSheet.annotations.insertNew(oCell.CellAddress, oCell.string)	
next i
End Sub


Sub deleteAnnotations
dim oSheet as object, oCell as object
dim i as long
oSheet = thiscomponent.sheets.getByName("Codes")
for i = oSheet.annotations.count -1 to 0 step -1
	oSheet.annotations.removeByIndex(i)
next 
end sub

Regards

[Neil-B]

Hi Pierre-yves, thank you for the attached example, I didn’t know that was even possible and its certainly an option that I will look for into,
Many thanks. Neil

[Ratslinger]

Hello,

From the start, I don’t have an answer to your ‘release mouse button to close dialog’. Have tried a few things without success.

Filling a list box in a dialog is easy:

DialogLibraries.LoadLibrary("Standard")
myDlg = CreateUnoDialog(DialogLibraries.Standard.YOUR_DIALOG)
lbField = myDlg.getControl("YOUR_LISTBOX")
lbField.addItem("LB Item One",0)
lbField.addItem("LB Item Two",1)
lbField.addItem("LB Item Three",2)

The two items you are entering using addItem are the verbiage itself (your cell content) and the second item is its position in the list. So retrieve the cell data and insert into list box.

You mention getting data from DB but I hesitate because of your comment. Twice it mentions MSQL (Hughes Technology) and once MYSQL ( seems more likely). You can use a macro to connect to a DB. Here is a connection to MySQL:

Dim oParms(1) as new com.sun.star.beans.PropertyValue 
oParms(0).Name = "user" 
oParms(0).Value = "root" 
oParms(1).Name = "password" 
oParms(1).Value =PASSWORD_HERE
oManager = CreateUnoService("com.sun.star.sdbc.DriverManager")
sURL = "sdbc:mysql:jdbc:localhost:3306/DATABASE_NAME"
oConnection = oManager.getConnectionWithInfo(sURL, oParms())
sSQL = "Select YOUR_FIELD(S) FROM YOUR_TABLE"
oStmt = oConnection.createStatement
oResult = oStmt.executeQuery(sSQL)

With that, you use oConnection to submit SQL then process the result set. Could run separately when document opens & store data in a global variable. A lot depends upon your knowledge of macros. Dealing with the cells may be best for you.

[Neil-B]

Hi Ratslinger, “MYSQL” I really should try to type slower, so less confusion
The example code above should work well for what I want, I will give it a try later on tonight.
The click the button to display and release to close was just my wishful thinking, that said a couple of keyboard shortcuts 1 to open and 1 to close might be an option when I get this working.
Many thanks neil