[Solved] Trying to center a value in a cell

[Hagar Delest]

Here is code with several tries.

ActiveSheet.getCellbyposition(n, 0).CellBackColor = RGB(255, 204, 153)


ActiveSheet.getCellbyposition(n, 0).VertJustify.CENTER()
'ActiveSheet.getCellbyposition(n, 0).VertJustify("com.sun.star.table.CellVertJustify.CENTER")
'ActiveSheet.getCellbyposition(n, 0).VertJustify = 2

The first line to change color works. I included that to hopefully show I'm pointing the correct place.

If I follow examples in documentation I get an error if com is not inside of quotes before even attempting to execute.

The only one that does not give a runtime error is the last one with 2 as value; it did not effect the results.

THanks in advance,

[FJCC]

This works for me:

Code: Select all   Expand viewCollapse view

oDoc = ThisComponent
Sheet = oDoc.Sheets.getByIndex(0)
Cell = Sheet.getCellByPosition(0,0)
Cell.VertJustify = com.sun.star.table.CellVertJustify.CENTER

[thepla]

I had that one before and I get error

Name 'com' is not declared.

I tried again and get the same results.

[FJCC]

I see that the defined constants of the OOo API are not available in VB. I used this VB code to open the file Test.ods and set the vertJustify of cell A1of the first sheet to CENTER

Code: Select all   Expand viewCollapse view

  Set oSM = CreateObject("com.sun.star.ServiceManager")
'Create the first and most important service
  Set oDesk = oSM.createInstance("com.sun.star.frame.Desktop")

  Set oDoc = oDesk.loadComponentFromURL("file:///c:/Test.ods", "_blank", 0, arg())

  Set Shts = oDoc.Sheets
  Set Sht = Shts.getByIndex(0)
  Set Cell = Sht.getCellByPosition(0, 0)
  Cell.VertJustify = 2

[thepla]

I am not by my working computer.

it looks like your example matches the one I have above that did actually process without error. My issue was a value of 2 did not actually center. Did yours center the value of "A1"?

Thanks,

[FJCC]

Yes, my code centers the text in the cell, and if I change the value of vertJustify to 1, the text is placed at the top of the cell. I wonder if ActiveSheet is really the object you expect it to be. I suggest replicating my version by creating a test document and using my exact code. If that works, you'll know that it can be done.
I'll be traveling the next few days, so it is likely I will not be on line until Monday.

[thepla]

myDoc = StarDesktop.loadComponentFromURL("private:factory/scalc", "_blank", 0, dummyArray)

Dim Shts As Object
Dim Sht As Object
Dim Cell As Object

Shts = myDoc.SHeets
Sht = Shts.getbyindex(0)
Sht.getCellbyposition(0, 0).setstring("SKU/SCAN")

Cell = Sht.getcellbyposition(0, 0)
Cell.vertjustify = 2


This opens up a spreadsheet and puts "SKU/SCAN" into cell A1 and does not center.

[turtle47]

and does not center

Do you want to try another code?

Code: Select all   Expand viewCollapse view

Cell.HoriJustify = 2

[thepla]

Don't know if I should call you a genius, me a fool, or wonder if there is something wrong.

I guess if Vertical is up and down I am a fool. Horizontal did center left to right.

Thanks a ton,

[pansmanse]

I realise that this is a very old post, but if anyone is still listening ...
Does this work in Writer text tables as well as Calc? Because I can't make vertical justify work. I've tried these, but no joy.

oCell.VertJustify = 2
oCell.VertJustify = com.sun.star.table.CellVertJustify.BOTTOM

[FJCC]

This works for me

Code: Select all   Expand viewCollapse view

oTables = ThisComponent.TextTables
oTbl = oTables.getByName("Table1")
oCell = oTbl.getCellByName("A1")
oCell.VertOrient = com.sun.star.text.VertOrientation.CENTER


[pansmanse]

Thanks for the prompt reply, FJCC. I believe my problem was that I was using a cell range rather than a single cell. This now works, but seems cumbersome.

oCell = oTable.getCellRangeByName("A1:A1") 'has to be range to set font size?
oCell.CharHeight = 12
oCell = oTable.getCellByName("A1")
oCell.VertOrient = 3

The reason for using a range is that it doesn't seem to be possible to set CharHeight for a single cell, but only a range. Off topic now, but any ideas?

[FJCC]

I confirmed that getting a single cell does not allow changing CharHeight but getting a range spanning one cell does allow it. I can't think of a way around that other than your solution or something very similar.

[pansmanse]

Thanks, FJCC.
Ah, the mysteries of Basic!