Ask Your Question
0

display cell data in a dialog list box

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

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
0

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

Ratslinger gravatar image

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

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.

edit flag offensive delete link more

Comments

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 +0100 )edit
1

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

pierre-yves samyn gravatar image

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

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

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
    oSheet.annotations.removeByIndex(i)
next 
end sub

Regards

edit flag offensive delete link more

Comments

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 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 27 times

Last updated: Mar 17