Page 1 of 1

[Solved] Creating Form without UserForm in Calc Basic

PostPosted: 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 207 times

Code: Select all   Expand viewCollapse view
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:

Re: Creating Form without UserForm in Calc Basic

PostPosted: 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

PostPosted: 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   Expand viewCollapse view
Print lastrow

program line.

Re: Creating Form without UserForm in Calc Basic

PostPosted: 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

PostPosted: 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!