Ask Your Question

display cell data in a dialog list box

asked 2019-03-16 19:58:45 +0200

Neil-B gravatar image

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

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2019-03-17 04:29:05 +0200

Ratslinger gravatar image

updated 2019-03-17 04:46:13 +0200


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:

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 
oParms(0).Name = "user" 
oParms(0).Value = "root" 
oParms(1).Name = "password" 
oParms(1).Value =PASSWORD_HERE
oManager = CreateUnoService("")
sURL = "sdbc:mysql:jdbc:localhost:3306/DATABASE_NAME"
oConnection = oManager.getConnectionWithInfo(sURL, oParms())
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.

edit flag offensive delete link more


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

Neil-B gravatar imageNeil-B ( 2019-03-17 18:34:41 +0200 )edit

answered 2019-03-17 12:26:00 +0200

pierre-yves samyn gravatar image

updated 2019-03-17 12:27:28 +0200


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).

image description

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.

image description

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
end sub


edit flag offensive delete link more


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

Neil-B gravatar imageNeil-B ( 2019-03-17 18:34:45 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-03-16 19:58:45 +0200

Seen: 62 times

Last updated: Mar 17