[Solved] Insert/remove text into a textbox (Calc)

[msemafor]

As I can't solve my problem I'd like to ask someone more experienced.I created simple dialog (4 fields) to let the user enter few data. After clicking "Submit" button those data should be inserted into textboxes put ON THE SHEET (not on any dialog). How to refer to those sheet texboxes in code to insert those data? Other thing is deleting those data from sheet textboxes after clicking button "Clear" on this sheet. Suppose it will be similar to inserting but how this piece of code should look like?
Thanks in advance.

[FJCC]

By "Textbox", I assume you mean the form control with that name. Here is some code recorded with MRI that set the text of such a control on Sheet1.

Code: Select all

 oSheets = ThisComponent.getSheets()
  oObj1 = oSheets.getByName("Sheet1")
  oDrawPage = oObj1.getDrawPage()
  
  oForms = oDrawPage.getForms()
  oObj2 = oForms.getByIndex(0)
  oObj3 = oObj2.getByName("Text Box 1")
  
  oObj3.setString("NewText")

[msemafor]

Thanks FJCC for quick reply. In my post I didn't mean textbox as a form control. Maybe I should describe it as a textfield created on the sheet. So data from a dialog should be inserted into this textfield. Before this project I did the same in Excel VBA and there I referred to Activesheet.OLEOBJECT.Shapes("name").Text="text"
This line of code doesn't work in LO Calc. I'm looking for an alternative working in LO Calc.

[FJCC]

Please upload a file with one of these objects on the first sheet. To upload a file, click Post Reply, click on the Attachments tab just below the box where you type a response, and use the Add Files button.

[msemafor]

Please use only blue button on the image under "2" digit. That's the one I'm working on now - clearing procedure. Generally all blue buttons wil be clearing fieldboxes depending on the number of that button counting from upper left side. All lines in "czysc_nr" procedure are going fine till the lines of putting an empty string to three fieldboxes placed on this image. Sorry for the mess in code but this is my first LO Calc project and I'm not a programmer/developer. I suppose inserting data from a dialog will go similar to putting an empty string (button "Create" just displays dialog and it's not connected yet to the next part of procedure).
Thanks a lot in advance for your help.

[JeJe]

I suggest you download MRI

https://extensions.openoffice.org/en/pr ... ction-tool

and explore your textboxes which are on the drawpage using that. eg

Code: Select all

mri thiscomponent.drawpages(0).getbyindex(13).getbyindex(0)

Edit:

Try this to clear one:

Code: Select all

.text.string = ""

[msemafor]

Thanks JeJe for quick reply. getbyindex twice? Could you please type the line of code? Will it work for users without MRI installed?

[JeJe]

This will clear the "Sample text2" textbox.

Code: Select all

thiscomponent.drawpages(0).getbyindex(30).control.string="" 

I got there by exploring with MRI - if you do that you'll be able to answer your own questions.

Edit:
users don't need it, its a tool for helping you write your code that you should find immensely useful.

[Lupp]

I simply don't understand.
There are lots of FormControl buttons, but nothing is told about a database connection.
I also don't understand the usefulness of the dialogs.
Even if the sheet actually gets connected to a database, its purpose obviously is to print selected labels (tickets / stickers), and the data don't need to be enterted, but selected from the DB then.
If the data shall be entered in that sheet, dialolgs are useless overhead. It's only a matter of defining the approprioate column widths and row heights, and you won't need special elements (FormControl in your sheet) to insert the items into your labels. Simply write into cells or connect them by simple formulas to those where you want to enter values, and which may be connected to FormControl objects. The FormControls themselves aren't even grouped with the related label shapes, and no macros are assigned in your case. ...
You may glance at the attached example.

aoo108314TicketLabels.ods

(47.12 KiB) Downloaded 92 times

[msemafor]

Thanks Lupp, interesting idea, will consider it.
Also thanks to JeJe for a line of code. As I'm complete newbie to LO coding so still have some questions. Installed MRI and how to start using it/getting to objects? Can I reffer to textfields by its names instead by its indexes? How to determine apriopriate texfield index/name?
Thanks a lot for all your advices and hints.

[robleyd]

[Tutorial] Introduction into object inspection with MRI

[FJCC]

Here is a revised version of your procedure for clearing the textboxes. Note that I had to change

Code: Select all

cena_txb = "cena" & i

to

Code: Select all

cena_txb = "Cena" & i

because the textbox is named Cena2.
I agree with Lupp that using macros for this seems unnecessary.

Code: Select all

Sub czysc_nr(oEvent)
Dim  oSheet As Object
Dim buttonName as String
Dim cena_txb
Dim cenajedn_txb
Dim nazwa_txb
Dim pole_txb as object
Dim i As Integer
Dim oForm as object
  Rem - Get control name
    buttonName = oEvent.Source.Model.Name
    oSheet=thiscomponent.getcurrentcontroller.activesheet

i = Mid(buttonName, 6, 1)

oForm = oSheet.getDrawPage().getForms().getByIndex(0)
nazwa_txb = "nazwa" & i
cena_txb = "Cena" & i
cenajedn_txb = "cenajedn" & i
oForm.getByName(cena_txb).Text = ""
oForm.getByName(nazwa_txb).Text = ""
oForm.getByName(cenajedn_txb).Text = ""

End Sub

[msemafor]

Huge thanks FJCC for your time and piece of code.
Must admit LO macros are more difficult then MS Excel ones so working on my project is not going quickly. Got stuck on another problem - how to unhide hidden textbox on the dialog depending on a value chosen by a user on the list on the same dialog in runtime? For example - dialog is displayed and choice is "ml" or "kg" or opak." --> numeric field appears. Other choice --> numeric field stays hidden.
I just want to prepare my project the same way as I did in MS Excel and to learn some more of LO Basic.
Thanks a lot for all hints and advices.

[RoryOF]

It is worth noting that under /Tools /Macros /Organise Macros/OpenOffice Basic : OpenOffice Macros : Tools are a number of macros worthy of investigation, including for ListBoxes, ModuleControls and Strings.

Probably similar location (mutatis mutandis) for LibreOffice.

[John_Ha]

Andrew Pitonyak's OpenOffice.org Macro Information is a very useful reference and guide.

[JeJe]

Got stuck on another problem - how to unhide hidden textbox on the dialog depending on a value chosen by a user on the list on the same dialog in runtime? For example - dialog is displayed and choice is "ml" or "kg" or opak." --> numeric field appears. Other choice --> numeric field stays hidden.
I just want to prepare my project the same way as I did in MS Excel and to learn some more of LO Basic.
Thanks a lot for all hints and advices.

If you show the form controls toolbar and turn design mode on using the button for that, then right click a textbox and select control from the menu. A dialog pops up with a tab called events which you can assign a macro to - including key pressed and released and text modified.

Using MRI to look at a textbox you can see there's a property called "visible" and it does what you'd expect- you write textbox.visible = true or false to show/hide.

[Edit: may be slightly different in LO as I just have OO as the mo. but will be similar if not the same]

[msemafor]

Wrote a piece of code to determine behaviour of numeric field and its frame - unhide both and set a label to a frame. Here is my code which doesn't work as want it to do:

Code: Select all

Sub jedn_miary_Change()
dim myDlg as object
	DialogLibraries.LoadLibrary("Standard")
	myDlg = CreateUnoDialog(DialogLibraries.Standard.formularz)

   if myDlg.getcontrol("jedn_miary").text = "ml" then
      myDlg.getcontrol("pojemn_ramka").Visible=true
      myDlg.getcontrol("pojemn_ramka").label="Pojemność [ml]"
      myDlg.getControl("pojemnosc").Visible=true
   elseif myDlg.getControl("jedn_miary").text = "kg" then
      myDlg.getControl("pojemn_ramka").Visible=true
      myDlg.getControl("pojemn_ramka").label="Waga [g]"
      myDlg.getControl("pojemnosc").Visible=true
   elseif myDlg.getControl("jedn_miary").text = "opak." then
	  myDlg.getControl("pojemn_ramka").Visible=true
	  myDlg.getControl("pojemn_ramka").label="Liczba szt. w opak."
      myDlg.getControl("pojemnosc").Visible=true
   else
      myDlg.getControl("pojemn_ramka").Visible=false
      myDlg.getControl("pojemnosc").Visible=false
   end if
End Sub

Tried to assign this macro to text modified event of the list on my dialog, state changed of the list event and start action event. Nothing works - textbox and its frame doesn't get visible after choosing "ml" or "kg" or "opak." in the list. I guess I might have defined something wrong in that code. Could you please help me with this? I'd be very grateful. Should I put spaces and capital letters manually in apriopriate places (ex. Visible = True instead of Visible=true)?

[JeJe]

You've lost me completely.

The textbox I was referring to in my last post was the textboxes in the document.

Dialog listboxes don't have a text modified event or the other events. They do have key pressed and released events.

Your code is showing a dialog then depending on the text of one of the controls of the dialog (which will be what it is set to in that dialog in the IDE) is showing or hiding other controls on the dialog. None of your code refers to the textboxes in the document drawpage.

Edit:
If you right click on the controls in your dialog in the IDE you can see the events which you can assign a macro to for those controls.

If you want something to happen when you change the text in one of those you have to use one of those events such as the key released event.

If you want to change the textboxes in the document or get the text in them they are accessed via the document drawpage as explained previously.

[msemafor]

Thank you JeJe for your reply. Yes, I meant controls in a dialog. Did assign that macro to events "Key released" and "Key pressed" of those controls. Everything seems to be as you told me to do. But still nothing happens when I choose one of three options from list on dialog. Where am I doing wrong? See file attached. Only four labels in upper row (red buttons "Utworz") are connected to dialog. Data from dialog doesn't get transfered yet to textfields on labels - that's my last step of this project.
Thanks a lot again in advance.

[JeJe]

The key released event isn't called when you select a combobox item with the mouse - which I presume is what you're doing. Key released will be called when you choose with the keyboard. The text modified or the item status changed might work for all.

[msemafor]

Assigned to text modified event bo still nothing is happening. I Suppose something in my code is wrong and ifs are incorectly proceding. But where and how it should be?

[JeJe]

Works for me. I assign the combo text modified to the Sub jedn_miary_Change() and it runs when I select a combo item with the mouse, giving a runtime error on the line

Code: Select all

if myDlg.getcontrol("jedn_ml").state = 1 then

as there is no such control on the dialog.

[msemafor]

Very sorry JeJe for attaching wrong file. On this one have created 4 radio buttons which I'm going to leave in place of a listbox. Macro is assign to state modified event and it runs after clicking on radios but displays an error about wrong method or property Visible. Maybe you know how the synthax should looks like here? Only first radio choice "szt." should not make numeric field "pojemnosc" visible, all the rest should display it.
Huge thanks in advance.

[JeJe]

So with MRI loaded you put this line in before the error

Code: Select all

  mri  myDlg.getModel().getByName("pojemn_ramka")

a box pups up showing you the properties of that control - and you can see it doesn't have a .visible property but it does have a .enablevisible. So you solve it yourself in a minute without asking... that's what you should be doing.

[msemafor]

Got all infos from MRI but maybe i just can't use them as a newbie. Besides all radio buttons return False or 0 while clicking and other controls don't unhide as I want them to do. Got stuck on this step of project and can't go through this. I can leave combobox if I'd manage to retrieve selected item to depend unhiding other controls or just radio buttons if state one of them will turn to True or 1 after clicking. Any help will be appreciated. Current file with some mess in coding attached. Don't rate the outfit of the sheet - it'll be improved at the end after preparing all functionalities.

[Villeroy]

If you would simply use a database instead of a spreadsheet ......

[msemafor]

I understand but in has to be done in calc.

[Villeroy]

No, that's wrong. It has to be done with something that actually works. Calc is an arithmetic calculator in the first place.
For stunts like this, you should use MS Excel. May be you find some idiot who programs this for you in Excel-VBA.

[msemafor]

I'm that idiot, I've done such file in MS Excel about 1,5 week ago. It works on users machines with a licence for MS Excel in company I work for. But unfortunately there are more machines with Libre Office, which users also wants to have this because it also do some accounting in one of textfield. Have never expected coding in LO will so much more difficult then in MS Excel. I declared preparing this project in LO too but I it's my first LO coding and that's why I'm asking for help here. Will also learn it by doing it.
Anyway, thanks Villeroy for nice words and your advice.

[JeJe]

Besides all radio buttons return False or 0 while clicking

If you set a macro to the item status changed event you can get which one was clicked.

Code: Select all

Sub radiobutton_statuschanged(ev) 'option button
msgbox ev.source.model.state '1 or 0
msgbox ev.source.state 'true or false
'ev.source.getcontext.getcontrol(controlname).model.state 'get another one's state.
End Sub