# Changing Sheetnames Macro

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 ) edit retag close merge delete ## 2 Answers Sort by » oldest newest most voted 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). more ## Comments 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! ( 2017-07-26 21:57:21 +0200 )edit @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. ( 2017-07-26 22:32:17 +0200 )edit 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 )
Next i
End Sub

more