Ask Your Question
0

Naming Sheet names to Cell Automatically (Macro!)

asked 2017-06-26 23:48:34 +0200

SmileAway gravatar image

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

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2017-06-27 01:24:44 +0200

librebel gravatar image

updated 2017-06-27 20:31:31 +0200

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.
edit flag offensive delete link more

Comments

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?

SmileAway gravatar imageSmileAway ( 2017-06-27 18:17:34 +0200 )edit

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

librebel gravatar imagelibrebel ( 2017-06-27 20:31:53 +0200 )edit

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

SmileAway gravatar imageSmileAway ( 2017-06-27 21:33:31 +0200 )edit

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

librebel gravatar imagelibrebel ( 2017-06-27 21:58:16 +0200 )edit

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 gravatar imageSmileAway ( 2017-07-26 16:46:41 +0200 )edit

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 gravatar imageSmileAway ( 2017-07-26 16:46:49 +0200 )edit

End Sub

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

Sub CellModify_disposing( oEvent ) End Sub

REM End

SmileAway gravatar imageSmileAway ( 2017-07-26 16:48:13 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-06-26 23:48:34 +0200

Seen: 572 times

Last updated: Jun 27 '17