Generating dropdown list with macro

Creating a macro - Writing a Script - Using the API

Generating dropdown list with macro

Postby machobymb » Fri May 24, 2019 8:49 am

Hi!

I'd like make a workbook. This WB load a text file, split lines to cells.
I'd like generate a dropdown list validation in every line last cell (the list items in other Sheet).
For example(this example my don't work):
oSheets = ThisComponent.getSheets()
oSheet1 = oSheets.getByName("Sheet1")
oCell = oSheet1.getCellRangeByName("M1")

oValidation = oCell.Validation
oValidation.setFormula1("Sheet1.A1:A3")
oCell.Validation = oValidation
LibreOffice 6.2.4 64bit on Debian testing & Win 10
machobymb
 
Posts: 9
Joined: Thu Feb 14, 2013 3:25 pm

Re: Generating dropdown list with macro

Postby Zizi64 » Fri May 24, 2019 8:56 am

Just a tip:
Use a Template file that contains the predefined validations...
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
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: 8147
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Generating dropdown list with macro

Postby machobymb » Fri May 24, 2019 9:31 am

This work for me in a empty sheet (I'll try to see if it works in my generated workbook):
Code: Select all   Expand viewCollapse view
Sub DropDown
   Dim FormulaStr1 as String
   Dim FormulaStr2 as String
   Sheet = ThisComponent.Sheets.getByIndex(0)
   FormulaStr1 = "$Sheet2.$A$1:$A$7"
   FormulaStr2 = "$Sheet2.$B$1:$B$3"

   For i = 0 To 10
      oCell = Sheet.getCellByPosition(0,i)
      Validation = oCell.Validation
      Validation.Type = com.sun.star.sheet.ValidationType.LIST
      Validation.setOperator  com.sun.star.Sheet.ConditionOperator.EQUAL
      Validation.setFormula1(FormulaStr1)
      oCell.Validation = Validation

      oCell = Sheet.getCellByPosition(1,i)
      Validation = oCell.Validation
      Validation.Type = com.sun.star.sheet.ValidationType.LIST
      Validation.setOperator  com.sun.star.Sheet.ConditionOperator.EQUAL
      Validation.setFormula1(FormulaStr2)
      oCell.Validation = Validation
   Next i
End Sub
LibreOffice 6.2.4 64bit on Debian testing & Win 10
machobymb
 
Posts: 9
Joined: Thu Feb 14, 2013 3:25 pm

Re: Generating dropdown list with macro

Postby Zizi64 » Fri May 24, 2019 9:38 am

This WB load a text file, split lines to cells.
I'd like generate a dropdown list validation in every line last cell (the list items in other Sheet).
For example(this example my don't work):
oSheets = ThisComponent.getSheets()

Where the macro was launched from?
Maybe it was launched from the text file. Then the "Thiscomponent" means: "the actual text document".

Please upload your ODF type sample files here.
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
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: 8147
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 3 guests