[Solved] Enter data using form to write into spreadsheet

[doncell6]

I am a LibreOffice novice.

I want to enter data into a Calc spreadsheet using a form. I was told Calc does not have forms.
I have been reading the documentation about Base. Apparently Base can read from a Calc spreadsheet but not write to it.

The data has to be in a spreadsheet as we need up to the minute pricing. I can get the pricing from my vendor using a DDE connection. The DDE connection only works with spreadsheets. I created a Calc spreadsheet and the DDE connection works fine there. So thanks to all who wrote LibreOffice to make that possible.

The data needs to be entered using a form because the employees make all kinds of mistakes if they enter the data directly into a spreadsheet. Excel has forms. We are currently using Excel but I want to abandon Excel and abandon using Windows operating system altogether.

Others must have faced the same problem. If a form cannot be created in Calc, what needs to enable a form in Base to enter data into a spreadsheet?

May be someone has a better idea? Perhaps someone could share a snippet of code showing how they solved this problem? I am fairly good at writing VBA code so may be this can be solved using macros in some way?

Thank you.

[Villeroy]

Where do your data come from? The solution to your problem might be completely unrelated to any kind of office suite.

[MTP]

Calc can have fields that are linked to cells on the spreadsheet - this might do what you want?

You'll want to have the "form controls" toolbar visible. Click on the pencil with the triangle (second icon from the left) to enter design mode. Click on the icon for the type of field you want on your form, and draw it where you want it (it can be resized and moved later if you're not happy with where it's put at first).

There are two ways to get to the field properties:
1 - right-click on the field and select "control"
2 - open the form navigator (make the "form design" toolbar visible, it is the compass over a square, sixth icon from the left), right-click on the field name in the form navigator windown, and select "properties"

In the field properties window, select the "data" tab. In the "linked cell" box, type in the location of the cell where you want to put whatever is typed into the field.

Now whoever enters data can type into the field (or fields, however many) and what they type will automatically be put into the linked cell.

[Villeroy]

Depending on the source, frequency, quantity, data types and shape of incomming data there may be some options to drop things into a database storage with no macro or with some tiny macro. However, knowing nothing about the raw material, it is impossible to give any further advice.

[doncell6]

Hello, all the data in manually entered.

The most difficult part is to create the DDE call to get pricing. Currently, the employees enter all the data (stock number, quantity, etc) Then they click on a button. The Excel spreadsheet closes and vba code creates the appropriate DDE call. The employee can then open the spreadsheet and the vendor's web site. The DDE call in the Excel file then starts getting prices in real time.

I want to develop some similar process using LibreOffice. I will try the idea MTP suggested.

Thank you all for your timely responses.

[Villeroy]

So you have a working Excel/VBA solution? Use it and forget LibreOffice.

[Justpat]

In Version 5.2


Select - "View"
"Toolbars"
"form controls".
Make sure design mode is on (toggle design mode on toolbar).
Go down to "More controls"

Hover your curser over icons in "More controls" toolbar until you find "Table control". Click it.

Draw a block in the spread sheet. a Table control wizard will appear from where you can choose your fields.

Make sure to turn off design mode in order to fill in the fields.