[Solved] Creating Form without UserForm in Calc Basic

Creating a macro - Writing a Script - Using the API

[Solved] Creating Form without UserForm in Calc Basic

Postby OrlandoS » Sun Mar 14, 2021 2:08 am


Hello, Everyone!

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

template sheet.ods
(26.15 KiB) Downloaded 216 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:
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
OrlandoS
 
Posts: 3
Joined: Sat Mar 13, 2021 11:50 pm

Re: Creating Form without UserForm in Calc Basic

Postby Villeroy » Sun Mar 14, 2021 3:07 am

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
Villeroy
Volunteer
 
Posts: 30291
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Creating Form without UserForm in Calc Basic

Postby Zizi64 » Sun Mar 14, 2021 9:15 am

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.
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.
User avatar
Zizi64
Volunteer
 
Posts: 10106
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Creating Form without UserForm in Calc Basic

Postby OrlandoS » Sun Mar 14, 2021 10:22 pm

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

Postby OrlandoS » Mon Mar 22, 2021 12:40 am

I found the solution. The problem was in three lines
of three codes in the attached spreadsheet. Thanks!
OpenOffice 3.1 on Windows
OrlandoS
 
Posts: 3
Joined: Sat Mar 13, 2021 11:50 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: Google [Bot] and 11 guests