I have a column of cells with data on a Calc sheet $C$4:$C$9. I would like this range of cell data to appear as a drop down pick list for a different cell $C$3 on the same sheet. I can do that using the menu approach Data>Validity… Allow: Cell range and then enter the range for the column of data. That is NOT what I am trying to do. Rather, I wish to assign the cell range programmatically using a BASIC macro so when new data is appended at $C$10 I an update the range in $C$3. I can use oSheetTest = ThisComponent.Sheets.getByName(“Cities”) oCellTest = oSheetTest.getCellRangeByName("$C$3") to access the desired cell but I am at a loss to figure out how to assign/modify the range of the column of data to that cell.
Hello,
Please see this answer → libreoffice calc python macro data validity list
It contains both Python and Basic code.
Thank you. One big help for documentation would be to type the variables used in examples. Once the data type is understood then the function becomes clear. With your help, here is what I learned. Given a String data type means the range can be easily manipulated. Thanks again.
'CityUpdatePickLists updates the city pick lists ranges to reflect the change
'in the number of cities in the city database.
Sub CityUpdatePickLists()
Dim oSheetTest as Object
Dim oValidityList as Object
Dim oValidation as Object
Dim sRange as String
oSheetTest = ThisComponent.Sheets.getByName("Cities") 'Sheet reference with cell C3
oCellTest = oSheetTest.getCellRangeByName("C3") 'Cell reference
oValidation = oCellTest.Validation
sRange = oValidation.getFormula1()
msgbox sRange 'The range is just a string.
End SubAll the variables in the sample are of type Object except for sList (listed within comments) which is type String. It may be you are referring to the ‘parameters’. These do not need defining as the quotes indicate these are strings.
Sorry, I didn’t mean the specific example. I was thinking of much of the documentation I found doing internet searches.
I also discovered that it necessary to do the following when setting the validity range on a cell:
oValidation.setFormula1(sRange)
oValidation.Type = 6
oValidation.setOperator(1)