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.
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 Sub
All 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.Type = 6