[Solved] Creating Form without UserForm in Calc Basic

Creating a macro - Writing a Script - Using the API
Post Reply
OrlandoS
Posts: 3
Joined: Sat Mar 13, 2021 11:50 pm

[Solved] Creating Form without UserForm in Calc Basic

Post by OrlandoS »


Hello, Everyone!

Why doesn't the following code work on the attached template sheet?

template sheet.ods
(26.15 KiB) Downloaded 325 times

Code: Select all

Sub ReadNSaveDialog1 ()

	cxtx_TextField1 = oDialog1.GetControl("cxtx_TextField1")
	cxcb_ComboBox1 = oDialog1.GetControl("cxcb_ComboBox1")
	cpdt_cpdt_DateField1 = oDialog1.GetControl("cpdt_DateField1")
	cxtx_TextField2 = oDialog1.GetControl("cxtx_TextField2")
	cxtx_TextField3 = oDialog1.GetControl("cxtx_TextField3")
	cxtx_TextField4 = oDialog1.GetControl("cxtx_TextField4")
	cpdt_DateField2 = oDialog1.GetControl("cpdt_DateField2")
	cpdt_DateField3 = oDialog1.GetControl("cpdt_DateField3")
	cxcb_ComboBox2 = oDialog1.GetControl("cxcb_ComboBox2")
	cxcb_ComboBox3 = oDialog1.GetControl("cxcb_ComboBox3")
	
	lastrow = Bt_Ok()	
	ThisComponent.Sheets(0).getCellByPosition(0, lastrow).setString( cxtx_TextField1.Text)
	ThisComponent.Sheets(0).getCellByPosition(1, lastrow).setString( cxcb_ComboBox1.Text)
	ThisComponent.Sheets(0).getCellByPosition(2, lastrow).setValue(CDate(cpdt_DateField1.Model.Text))
	ThisComponent.Sheets(0).getCellByPosition(3, lastrow).setString( cxtx_TextField2.Text)
	ThisComponent.Sheets(0).getCellByPosition(4, lastrow).setString( cxtx_TextField3.Text)
	ThisComponent.Sheets(0).getCellByPosition(5, lastrow).setString( cxtx_TextField4.Text)
	ThisComponent.Sheets(0).getCellByPosition(6, lastrow).setValue(CDate(cpdt_DateField2.Model.Text))
	ThisComponent.Sheets(0).getCellByPosition(7, lastrow).setValue(CDate(cpdt_DateField3.Model.Text))
	ThisComponent.Sheets(0).getCellByPosition(8, lastrow).setString( cxcb_ComboBox2.Text)
	ThisComponent.Sheets(0).getCellByPosition(9, lastrow).setString( cxcb_ComboBox3.Text)
		
	cxtx_TextField1.Text = ""
	cxcb_ComboBox1.Text = ""
	cpdt_cpdt_DateField1.Model.Text = ""
	cxtx_TextField2.Text = ""
	cxtx_TextField3.Text = ""
	cxtx_TextField4.Text = ""
	cpdt_DateField2.Model.Text = ""
	cpdt_DateField3.Model.Text = ""
	cxcb_ComboBox2.Text = ""
	cxcb_ComboBox3.Text = ""

End Sub
Ps: an error appears on the line, ThisComponent.Sheets(0).getCellByPosition(2, lastrow).setValue(CDate(cpdt_DateField1.Model.Text))

I would also like to:
1) Show selection list in Label2, Label9 and Label10, as a data validation that I will add in columns L, N and P;
2) Transfer the data to the spreadsheet on the line where the cursor is stopped (without automatic line change),
preserving the formatting in the table and keeping the form open with the transferred data.

Grateful for the attention!
Orlando Souza

:geek:[/color]
Last edited by Hagar Delest on Mon Mar 22, 2021 4:58 pm, edited 3 times in total.
Reason: tagged solved.
OpenOffice 3.1 on Windows
User avatar
Villeroy
Volunteer
Posts: 30611
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Creating Form without UserForm in Calc Basic

Post by Villeroy »

You can write thousands and thousands of lines of Basic code. Your spreadsheet will never be a database.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Zizi64
Volunteer
Posts: 10428
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Creating Form without UserForm in Calc Basic

Post by Zizi64 »

What is the code of the custom function "Bt_Ok()"?

Check the result of the function before you use it (by a temporary

Code: Select all

Print lastrow

program line.
Tibor Kovacs, Hungary; LO6.1.6, 6.4.7 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.1.1;AOO4.1.9
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
OrlandoS
Posts: 3
Joined: Sat Mar 13, 2021 11:50 pm

Re: Creating Form without UserForm in Calc Basic

Post by OrlandoS »

Hi, Villeroy!
I only know the "Dialog" in Basic-Calc that reminds me of the "UserForm" in VBA-Excel.

Hi, Zizi64!
I posted the custom function code "Bt_Ok ()" in the previous attachment.
OpenOffice 3.1 on Windows
OrlandoS
Posts: 3
Joined: Sat Mar 13, 2021 11:50 pm

Re: Creating Form without UserForm in Calc Basic

Post by OrlandoS »

I found the solution. The problem was in three lines
of three codes in the attached spreadsheet. Thanks!
OpenOffice 3.1 on Windows
Post Reply