[Solved] Enable Data->Validity Via Macro

[s_ha_dum]

Is it possible to enable Data->Validity using a macro?

I'm using a macro to create a new calc document from selected parts of another calc document and I'd really like on of those columns to be a simple Yes/No select menu of the kind I'd make by going to Data->Validity, selecting 'List', and typing my yes and no values if I were doing this manually. I found information about creating the document, saving it, copying the values, and formatting the cells, but not about enabling data validity. Is this possible?

[Villeroy]

http://api.openoffice.org/docs/common/r ... ation.html

Description
represents the data validation settings for a cell or cell range.

Developers Guide
Spreadsheets - Other Table Operations - Data Validation

The description is not exactly right. Multiple ranges (c.s.s.SheetCellRanges) support this service as well.

[s_ha_dum]

Thank you Villeroy. I could never find just the right search terms to get there. Much appreciated.

[Villeroy]

Install http://extensions.services.openoffice.org/project/MRI (Tools>Extensions... <downloaded file>[Add])
Restart the office.
Set up some validated range.
Select the range.
Tools>Add-ons>MRI<--selection
Double-click property "Validation"

[s_ha_dum]

I've actually ended up doing this:

Code: Select all

newdocsheet.Columns(4).Width = 900
usedarea = newdocsheet.getCellRangeByPosition(4,1,4,t-1)
oProps = usedarea.Validation	
oProps.Type = com.sun.star.sheet.ValidationType.LIST
oProps.ShowErrorMessage = True 
oProps.ErrorMessage = "Must be a value from the list!" 
oProps.ErrorTitle = "ERROR"
oProps.ShowInputMessage = True 
oProps.InputTitle = "INPUT VALUES:" 
oProps.InputMessage = "Take a value from the list" 
oProps.ErrorAlertStyle = com.sun.star.sheet.ValidationAlertStyle.STOP 
oProps.Formula1 = """Yes"";""No"""
usedarea.Validation = oProps

... which was cribbed mostly from here and here. It was your post that gave me the keywords to get there though. So thanks again.

[bartjeman]

I'm working on a similar problem. Difference is that I do not know the absolute cell address. Based on the string in the current cell (that is triggering the event) I need to create a validation list in the left adjacent cell. Problem is I get runtime error "arguments len difer" on line:
Validation.setFormula1 = """33"";""39"";""45"";""51"";""57"";""63"";""69"";""75"";""81"";""87"""
Thanks in advance for any help with this.

Code: Select all

REM  *****  BASIC  *****
sub Main
dim document   as object
dim dispatcher as object
dim oCell as object
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

Set oCell = ThisComponent.getCurrentSelection
SectionType = oCell.getString
dim args1(1) as new com.sun.star.beans.PropertyValue
args1(0).Name = "By"
args1(0).Value = 1
args1(1).Name = "Sel"
args1(1).Value = false
dispatcher.executeDispatch(document, ".uno:GoLeft", "", 0, args1())
Set oCell = ThisComponent.getCurrentSelection
Set Validation = oCell.Validation
Validation.Type = com.sun.star.sheet.ValidationType.LIST
Validation.ShowErrorMessage = True
Validation.ErrorMessage = "Must be a value from the list!"
Validation.ErrorTitle = "ERROR"
Validation.ShowInputMessage = True
Validation.InputTitle = "INPUT VALUES:"
Validation.InputMessage = "Take a value from the list"
Validation.ErrorAlertStyle = com.sun.star.sheet.ValidationAlertStyle.STOP
Validation.setOperator  com.sun.star.Sheet.ConditionOperator.EQUAL
Select Case SectionType
  Case "w"
  Validation.setFormula1 = """33"";""39"";""45"";""51"";""57"";""63"";""69"";""75"";""81"";""87"""
  Case "d"
  Validation.setFormula1 = """57"";""63"";""69"";""75"";""81"";""87"""
  Case else
  MsgBox "wrong entry, must be: d, w, k or p"
  End Select
Validation.ShowList = com.sun.star.sheet.TableValidationVisibility.SORTEDASCENDING
Set oCell.Validation = Validation
End Sub

[FJCC]

setFormula takes an argument, so the code would look like

Code: Select all

Select Case SectionType
  Case "w"
  Validation.setFormula1( """33"";""39"";""45"";""51"";""57"";""63"";""69"";""75"";""81"";""87""")
  Case "d"
  Validation.setFormula1("""57"";""63"";""69"";""75"";""81"";""87""")
  Case else
  MsgBox "wrong entry, must be: d, w, k or p"
  End Select

[bartjeman]

Thanks!