[Solved] Enter data using form to write into spreadsheet

Creating and using forms

[Solved] Enter data using form to write into spreadsheet

Postby doncell6 » Sun Aug 04, 2013 5:05 am

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.
Last edited by doncell6 on Sun Sep 08, 2013 5:43 am, edited 1 time in total.
Version 4.3.7.2 on Windows 7 Pro
doncell6
 
Posts: 72
Joined: Wed Jul 24, 2013 6:26 am

Re: Enter data using form to write into spreadsheet

Postby Villeroy » Sun Aug 04, 2013 2:07 pm

Where do your data come from? The solution to your problem might be completely unrelated to any kind of office suite.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24656
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Enter data using form to write into spreadsheet

Postby MTP » Sun Aug 04, 2013 7:32 pm

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.
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
User avatar
MTP
Volunteer
 
Posts: 1618
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: Enter data using form to write into spreadsheet

Postby Villeroy » Sun Aug 04, 2013 7:50 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24656
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Enter data using form to write into spreadsheet

Postby doncell6 » Mon Aug 05, 2013 5:28 am

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.
Version 4.3.7.2 on Windows 7 Pro
doncell6
 
Posts: 72
Joined: Wed Jul 24, 2013 6:26 am

Re: Enter data using form to write into spreadsheet

Postby Villeroy » Mon Aug 05, 2013 1:43 pm

So you have a working Excel/VBA solution? Use it and forget LibreOffice.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24656
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Enter data using form to write into spreadsheet

Postby Justpat » Mon Sep 04, 2017 11:23 pm

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.
OpenOffice 5.2 on Windows 10
Justpat
 
Posts: 1
Joined: Mon Sep 04, 2017 11:03 pm


Return to Forms

Who is online

Users browsing this forum: No registered users and 6 guests