Changing Sheetnames Macro

[SmileAway]

Hi!

I have a spreadsheet with 10 sheets. I’m trying to figure out how to rename the sheets to whatever I put in a specific cell such as P3. Sheet number 1 works with the code below, but I can’t seem to get it working for the other sheets.

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")
    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  *****   

(Code formatted as code by @Lupp )

[Ratslinger]

Hello,

Your line of code:

oSheet = ThisComponent.CurrentController.ActiveSheets (i)

is incorrect. There is no ActiveSheets and there is no index with it. You are referring to the currently active sheet. The line should be:

oSheet = ThisComponent.CurrentController.ActiveSheet

to get the value in “P3”.

Now if you are looking to change the name of a sheet other than the active sheet, you can use:

oTargetSheet = ThisComponent.getSheets().getByName("SHEET_NAME_HERE")

but you would have to change the code every time the sheet name changes. Instead:

oTargetSheet = ThisComponent.getSheets().getByIndex(1)

gets the wanted sheet by way of an index with the first sheet having an index of 0. Once you have the wanted sheet you can change the name with:

oTargetSheet.Name = oCell.String

Edit 7/26/2017:

Just to be clear, here is what happens if you try to duplicate a sheet name:

The sheet name is used to identify the uniqueness of cell A1 on the first sheet from cell A1 on other sheets.

Further Edit:

If you wanted to retain the same name for all sheets with a unique number appended:

Sub ChangeSheetname
  Dim oSheet		as Object
  Dim oTargetSheet  as Object
  Dim oCell 		as Object
  Dim iSheetIndex	as Integer
  Dim iIndex		as Integer
  oSheet = ThisComponent.CurrentController.ActiveSheet
  oCell = oSheet.getCellRangeByName("P3")
  iSheetIndex = ThisComponent.getSheets().Count
  for iIndex = 0 to iSheetIndex-1
      oTargetSheet = ThisComponent.getSheets().getByIndex(iIndex)
      oTargetSheet.Name = oCell.String & (iIndex + 1)
  next iIndex
End Sub

By obtaining the sheet count and using that for the index, it doesn’t matter how many sheets there are. It will apply the “P3” name and append an incremented number.

If this answers your question please click on the (upper left area of answer).

[SmileAway]

So I was digging around a bit more and made edits to the original post. I tried your way, but it didn’t work. I did get the above post working, however, it only updates the 1st sheet, or whichever sheet I open up first. I have the listener on ‘open document’ in customizations. Not sure what else I could be missing. Thanks for your help!

[Ratslinger]

@Lupp Sorry for comment, just discovered where change came from.

@SmileAway The code given was tested and works. It’s possible it is not clear what you are attempting to do. My understanding is to change a sheet name based upon the contents of a cell. What determines which sheet name of the ten you are changing? You can’t be changing all of them to the same name.

[librebel]

Hello @SmileAway,

The above code only works for 1 Sheet, because the ModifyListener is set only once for 1 cell.

To set the ModifyListener for all cells P3 in all your Sheets, adjust the method as follows:

Sub SetModifyListener()
REM call this method once to set the ModifyListener for all Sheets.
REM To destroy the listener, call RemoveModifyListener().
REM ( change "P3" to the cell which should trigger the modified() callback if modified )
REM ( it's the same cell in all sheets).
    Dim oSheet As Object, i As Integer
    Const strCellAddress$ = "P3"    REM Your Cell Address here.
    oModifyListener = createUnoListener("CellModify_","com.sun.star.util.XModifyListener")
	For i = 0 to ThisComponent.Sheets.getCount() - 1
		oSheet = ThisComponent.Sheets.getByIndex( i )
		oCell = oSheet.getCellRangebyName( strCellAddress )
    	oCell.addModifyListener( oModifyListener )
	Next i
End Sub

[SmileAway]

OMG! It works! I’m so happy, thank you soooo much lilrebel You are the best!