Ask Your Question
0

How to assign/modify validity range in Calc using Basic Macro.

asked 2020-02-19 00:20:26 +0200

OldEng gravatar image

updated 2020-07-20 22:53:15 +0200

Alex Kemp gravatar image

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.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2020-02-19 00:42:20 +0200

Ratslinger gravatar image

Hello,

Please see this answer -> libreoffice calc python macro data validity list

It contains both Python and Basic code.

edit flag offensive delete link more

Comments

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
OldEng gravatar imageOldEng ( 2020-02-19 14:11:26 +0200 )edit

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.

Ratslinger gravatar imageRatslinger ( 2020-02-19 20:49:01 +0200 )edit

Sorry, I didn't mean the specific example. I was thinking of much of the documentation I found doing internet searches.

OldEng gravatar imageOldEng ( 2020-02-20 17:50:56 +0200 )edit

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)

OldEng gravatar imageOldEng ( 2020-02-20 17:58:48 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-02-19 00:20:26 +0200

Seen: 113 times

Last updated: Feb 19