[Solved] Open CellStyle dialog with StarBasic

[joesch]

Hello,

Some years ago there was a (unfortunately unresolved) thread about opening the CellStyle dialog via Basic, see:
viewtopic.php?f=45&t=83211

Is there a solution in the meantime?

Note:
Maybe the former questioner (Mr.Dandy) has found a solution, but unfortunately I am newly registered so that I cannot send "Mr.Dandy" a direct message.


Greetings,
joesch

[joesch]

It is unbelievable (because I had already tried a lot of things without success), but I just now found(*) a solution:

Code: Select all

sub OpenCellStyleDialog_NewStyle()
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args(0) as new com.sun.star.beans.PropertyValue
args(0).Name = "Family"
args(0).Value = 2

dispatcher.executeDispatch(document, ".uno:NewStyle", "", 0, args())

end sub

Greetings,
joesch


(*)
I have recorded "New template from selection" via macro recorder and received:

Code: Select all

sub Main
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(1) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Param"
args1(0).Value = "test"
args1(1).Name = "Family"
args1(1).Value = 2

dispatcher.executeDispatch(document, ".uno:StyleNewByExample", "", 0, args1())

end sub

From this I could read the property value "Family" and combine it with ".uno:NewStyle"[/size]

[Zizi64]

Here is an example code, hoy you can get the style name of the selected cell, and then edit the cell style of the selected cell (in the built-in dialog):

EditStyle.ods

(12.7 KiB) Downloaded 203 times

Just select a cell formatted by the built-in cell styles, and then click on the button (the macro is assigned to the button), but you can assign the macro to a new Menu item.

Code: Select all

Sub EditStyle
rem ----------------------------------------------------------------------
rem define variables
dim oDoc as object
Dim oSelection as object
Dim oSheet as object
Dim oCell as object
Dim sCellStyleName as string

dim oDispatcher as object
Dim Args1(1) as new com.sun.star.beans.PropertyValue
rem ----------------------------------------------------------------------
rem get access to the document
oDoc   = ThisComponent.CurrentController.Frame
oSelection = ThisComponent.CurrentSelection
if NOT oSelection.SupportsService("com.sun.star.sheet.SheetCell")  then exit sub
oCell = oSelection
sCellStyleName = oCell.CellStyle
rem Print sCellStyleName  

oDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

Args1(0).Name = "Param"
Args1(0).Value = sCellStyleName 
Args1(1).Name = "Family"
Args1(1).Value = 2

oDispatcher.executeDispatch(oDoc, ".uno:EditStyle", "", 0, Args1())

End Sub

[joesch]

Here is an example code, hoy you can get the style name of the selected cell, and then edit the cell style of the selected cell

Yes, interesting, but not my intention.

OK... maybe my formulation "Open CellStyle Dialog" was irritating, but please look on the screenshot in the linked thread:
viewtopic.php?f=45&t=83211

the title of the dialog is only "Cell Style" there and that's what I was talking about. The dialog for editing an existing style has the name of the (existing) style in the title.


Greetings,
joesch

[Zizi64]

The linked dialog box layout will be appeared, when you want to CREATE a NEW STYLE. The dialog box is same, but it has different settings.

This is a different task.

You can open it
- as "StyleNewByExample": in this case the new style will inherit the properties of the example style/direct formatting properties.
- as "NewStyle": in this case there is not a sample style.

Of course the passed parameters must be different in the different cases.

Please study the API descriptions of the AOO/LO, and the Andrew Pitonyak's free macro programming books.
API: Application Programming Interface.

[joesch]

The linked dialog box layout will be appeared, when you want to CREATE a NEW STYLE.

Yes, exactly. And that's the dialog I wanted to display, not the dialog for editing an existing style.

Please study the API descriptions of the AOO/LO, and the Andrew Pitonyak's free macro programming books.

I'm familiar with this information, I've been a volunteer (https://people.apache.org/committer-index.html#joesch) in the OO project (first OOo, now AOO) for about 15 years and have been programming in StarBasic since then.


Greetings,
joesch

[Zizi64]

EditAndCreateStyle.ods

(15.42 KiB) Downloaded 202 times

The third button works for me in my LO 6.2.8 only, but not in my AOO 4.1.5 portable

[joesch]

Are you sure that "Param" expects a property value array?
OK, "Param" sounds like a Property-Value-Array but i think "Param" musst be a simple string as name for the new template, because that's what the macro recorder records.

The following works for me, for LO and AOO (If you want it to look better you can replace the InputBox with a dialog.):

Code: Select all

Sub NewStyleBasedOnASample
rem ----------------------------------------------------------------------
rem define variables
Dim oDoc as object
Dim oSelection as object
Dim oSheet as object
Dim oCell as object
Dim sCellStyleName as string

dim oDispatcher as object
Dim Args1(1) as new com.sun.star.beans.PropertyValue
rem ----------------------------------------------------------------------
rem get access to the document
oDoc   = ThisComponent.CurrentController.Frame
oSelection = ThisComponent.CurrentSelection
if NOT oSelection.SupportsService("com.sun.star.sheet.SheetCell")  then exit sub

oDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

csn = Inputbox("Please enter a name for the new cellstyle","Create cellstyle")

Args1(0).Name = "Param"
Args1(0).Value = csn
Args1(1).Name = "Family"
Args1(1).Value = 2

oDispatcher.executeDispatch(oDoc, ".uno:StyleNewByExample", "", 0, Args1())

End Sub

Greetings,
joesch

[Villeroy]

It does even create a sub-style of the current selected style.
And it replaces the stlye when you use the same name for the same parent style twice.
Sometimes dispatch macros do the right thing without excessive coding.