Page 1 of 1

Generating dropdown list with macro

Posted: Fri May 24, 2019 8:49 am
by machobymb
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

Re: Generating dropdown list with macro

Posted: Fri May 24, 2019 8:56 am
by Zizi64
Just a tip:
Use a Template file that contains the predefined validations...

Re: Generating dropdown list with macro

Posted: Fri May 24, 2019 9:31 am
by machobymb
This work for me in a empty sheet (I'll try to see if it works in my generated workbook):

Code: Select all

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

Re: Generating dropdown list with macro

Posted: Fri May 24, 2019 9:38 am
by Zizi64
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.