Calc: Print with all dropdown list items

[Timur]

Hi, In Calc,I have a cell that is made with Data-Validity and it’s value is used to calculate sheet.
How coud I print calculated sheets with all dropdown list items? Single PDF would be fine.

Could not find that for LO.
I found the following macro for MSO but coiuld not try it as Range is not available in LO:

Sub MS_Print_Thru_Data_Validation_AY10()
Dim xRg As Range
Dim xCell As Range
Dim xRgVList As Range
Set xRg = Worksheets(“ASD-1032 02 23 PERIOD”).Range(“AY10”)
Set xRgVList = Evaluate(xRg.Validation.Formula1)
For Each xCell In xRgVList
xRg = xCell.Value
ActiveSheet.PrintOut
Next
End Sub

[Zizi64]

Please upload an ODF type (simple and small) sample file here.

[Timur]

Sample can be answer from How to create dynamic Validity list using formula? - #2 by karolus. It has dropdown entries in Sheet1:B.
Better solution would specify single cell to iterate thru dropdown, but without it I could use a single dropdown per sheet.

[Timur]

I tried to adjust, but it says that DataValidation or Validation property or method is not valid.

oSheet = ThisComponent.CurrentController.ActiveSheet
oCell = ThisComponent.CurrentSelection
oRange = oCell.getRangeAddress
oList = oRange.DataValidation.Formula1

[sokol92]

If the data source for validation the cell value is specified as the address of cell range or the named range, then the data source can be obtained as follows:

Option Explicit
Sub DefSrcList()
  Dim oCell As Object, oSheet As Object, oSrcList As Object
  oCell=ThisComponent.CurrentSelection
  oSheet=oCell.Spreadsheet
  oSrcList=oSheet.getCellRangeByName(oCell.Validation.Formula1)
End Sub

If (array) formula is specified to determine the data source (as in your link), then the way will be much longer...

[Timur]

@sokol92

  oSrcList=oSheet.getCellRangeByName(oCell.Validation.Formula1)

I get and exception here.

[sokol92]

Try it on the attached file.
Select cell A1 and run the macro.
TestValidation.ods (10.6 KB)

[Timur]

Same exception. Tried with LO 7.5.9.2.

[sokol92]

I checked with a “clean” profile.
No error occurs.
Version: 7.6.4.1 (X86_64) / LibreOffice Community
Build ID: e19e193f88cd6c0525a17fb7a176ed8e6a3e2aa1
CPU threads: 6; OS: Windows 10.0 Build 19045; UI render: Skia/Vulkan; VCL: win
Locale: ru-RU (ru_RU); UI: ru-RU
Calc: CL threaded

[Timur]

Thanks, this is OK in new profile. IIUC, that solves range part, but not print part .
MS sample has a loop, which uses range variables. What would be xCell/xRg in LO and does .Value work with that.

I also do not undestand why it matters how data source for validation is made. Finally, is it not some value, that can be referenced as cell value? Regarding the difference: address of cell range or the named range vs. (array) formula is specified to determine the data source.

[sokol92]

@Timur

this is OK in new profile

Great, otherwise I had no hypotheses.

@Timur

MS sample has a loop, which uses range variables. What would be xCell/xRg in LO and does .

In the example, the xRgVList variable after assignment contains a reference to the range - the source of data for validation. Next, in a loop through all cells of the data source, the value of the source cell is transferred to cell xRg (sheet ASD-1032 02 23 PERIOD, cell AY10).
In LO it looks like this (haven’t tested it on a computer):

xRg = oDoc.Sheets.GetByName("ASD-1032 02 23 PERIOD").getCellRangeByName("AY10")
xRgVList = xRg.Spreadsheet.getCellRangeByName(xRg.Validation.Formula1)

@Timur

also do not undestand why it matters how data source for validation is made.

As indicated in the Validity dialog for the Source control, there are three options for setting the data source:

1. Specify the address or range name
2. Specify a formula that returns a reference to a range of cells
3. Specify a formula that returns an array.

The Excel Application.Evaluate method is very powerful (despite the sparse description) and handles all three of the above cases.
In LO for option 1, I provided sample code above.
For options 2 and 3, we can try assigning a formula (this is an array formula) to a free cell and reading the result from the resulting range of cells. This is a long way, although possible…