Page 1 of 1

[Solved] Creating Form without UserForm in Calc Basic

Posted: Sun Mar 14, 2021 2:08 am
by OrlandoS

Hello, Everyone!

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

template sheet.ods
(26.15 KiB) Downloaded 333 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


Re: Creating Form without UserForm in Calc Basic

Posted: Sun Mar 14, 2021 3:07 am
by Villeroy
You can write thousands and thousands of lines of Basic code. Your spreadsheet will never be a database.

Re: Creating Form without UserForm in Calc Basic

Posted: Sun Mar 14, 2021 9:15 am
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.

Re: Creating Form without UserForm in Calc Basic

Posted: Sun Mar 14, 2021 10:22 pm
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.

Re: Creating Form without UserForm in Calc Basic

Posted: Mon Mar 22, 2021 12:40 am
by OrlandoS
I found the solution. The problem was in three lines
of three codes in the attached spreadsheet. Thanks!