Naming Sheet names to Cell Automatically (Macro!)

[SmileAway]

Hi everyone,

I’ve been at this for a few hours now and can’t seem to get a Macro working.
So what I’d like to do is for Cell B3 (or something) to be populated with a name and it’ll automatically rename the current sheet (I plan to have about 5 sheets!).

In Excel I am able to create a working macro, but I’m too great using LibreOffice. Any help or guidance would be wonderful! Thank you

[librebel]

Hello @SmileAway,
you could set a Listener to cell B3, then you receive a callback when the cell is modified:

REM  *****  ModifyListener  *****

Global oModifyListener As Object
Global oCell As Object

Sub SetModifyListener()
REM call this method once to set the ModifyListener.
REM to destroy the listener, call RemoveModifyListener().
REM ( change "B3" to the cell which should trigger the modified() callback if modified )
	Dim oSheet As Object
	Const strCellAddress$ = "B3"	REM Your Cell Address here.
	oModifyListener = createUnoListener("CellModify_","com.sun.star.util.XModifyListener")
	oSheet = ThisComponent.CurrentController.ActiveSheet
	oCell = oSheet.getCellRangebyName( strCellAddress )
	oCell.addModifyListener( oModifyListener )
End Sub

Sub RemoveModifyListener()
    If Not IsNull( oCell ) Then oCell.removeModifyListener( oModifyListener )
End Sub

Sub CellModify_modified( oEvent )
	oEvent.Source.getSpreadsheet.setName( oEvent.Source.getString )
End Sub

Sub CellModify_disposing( oEvent )
End Sub

REM  *****  End  *****

EDIT 1:
i made a few adjustments in the code above:

Global oModifyListener As Object
Global oCell As Object

and:

Sub RemoveModifyListener()
    If Not IsNull( oCell ) Then oCell.removeModifyListener( oModifyListener )
End Sub

EDIT 2:
To set the ModifyListener automatically when your document opens:

1) select the main menu "Tools:Customize..."
2) in the dialog that pops up, select the tab "Events"
3) in the Events listbox, select the item "Open Document" ,
4) click the "Macro..." button,
5) in the Macro Selector dialog that pops up, locate your macro "SetModifyListener()" and click OK,
6) back in the Events listbox, select the item "Document is going to be closed",
7) click the "Macro..." button again,
8) in the Macro Selector dialog that pops up, locate your macro "RemoveModifyListener()" and click OK.

[SmileAway]

Wonderful, thank you very much! If you don’t mind, now that this works. I can’t seem to run this without having to “run macro” each time. Is there a way to automate this?

[librebel]

Hello SmileAway,
Yes that is possible, please see the EDITS in my answer.

[SmileAway]

Hi!
So far I’ve got this Macro working when I click run macro, and save it (or depending on what I set in Customize > Events). But I still can’t get it working! when I try to use the listener Macro you helped me with earlier. Thought it was working but this Macro below helps me change the sheetname

Sub ChangeSheetname
Dim oSheet
Dim oCell

  oSheet = ThisComponent.CurrentController.ActiveSheet
  oCell = oSheet.getCellRangeByName("P3")
  oSheet.Name = oCell.String

End Sub

[librebel]

After connecting the method SetModifyListener() to the “Open Document” event, close your document and then reopen it, and then set cell B3.

[SmileAway]

Hi lilrebel! So I got your code working after recreating a brand new spreadsheet, but it just works for 1 out of the 10 sheets that I have.

The code that works from you is posted below. Do you have thoughts on that? I tried setting 'Customize" options like you mentioned, but it still only changes one sheet name and does not have an effect on the others. Thank you again for all your help!

[SmileAway]

REM ***** ModifyListener *****

Global oModifyListener As Object
Global oCell As Object

Sub SetModifyListener()
REM call this method once to set the ModifyListener.
REM to destroy the listener, call RemoveModifyListener().
REM ( change “P3” to the cell which should trigger the modified() callback if modified )
Dim oSheet As Object
Const strCellAddress$ = “P3” REM Your Cell Address here.
oModifyListener = createUnoListener(“CellModify_”,“com.sun.star.util.XModifyListener”)

[SmileAway]

End Sub

Sub CellModify_modified( oEvent )
oEvent.Source.getSpreadsheet.setName( oEvent.Source.getString )
End Sub

Sub CellModify_disposing( oEvent )
End Sub

REM ***** End *****