Page 1 of 1
[Solved] Enable Data->Validity Via Macro
Posted: Fri Dec 04, 2009 10:06 pm
by 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?
Re: Enable Data->Validity Via Macro
Posted: Sat Dec 05, 2009 3:51 pm
by Villeroy
http://api.openoffice.org/docs/common/r ... ation.html
The description is not exactly right. Multiple ranges (c.s.s.SheetCellRanges) support this service as well.
Re: Enable Data->Validity Via Macro
Posted: Sat Dec 05, 2009 6:40 pm
by s_ha_dum
Thank you Villeroy. I could never find just the right search terms to get there. Much appreciated.
Re: Enable Data->Validity Via Macro
Posted: Sat Dec 05, 2009 8:52 pm
by 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"
Re: Enable Data->Validity Via Macro
Posted: Sat Dec 05, 2009 10:40 pm
by 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.
Re: [Solved] Enable Data->Validity Via Macro
Posted: Sun Jan 03, 2010 7:50 am
by 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
Re: [Solved] Enable Data->Validity Via Macro
Posted: Sun Jan 03, 2010 3:48 pm
by 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
Re: [Solved] Enable Data->Validity Via Macro
Posted: Mon Jan 04, 2010 5:03 am
by bartjeman
Thanks!