Error '9': Subscript out of range when trying to change codename of a sheet

[bvs123]

Hi all,

Here is the case:

I have to:
1) Add a new worksheet
2) Name the worksheet "MATLAB Input"
3) Change the codename of the worksheet to "Sheet4"

I use this code:

Sheets.Add
ActiveSheet.Name = "MATLAB Input"
ActiveSheet.Move After:=ActiveWorkbook.Sheets("Producthistorie")
ThisWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).Name = "Sheet4"

It works fine when I run it from VBE (play button). However when I call this macro from a button on a sheet I get error 9: Subscript out of range. How to do this?

Best regards,


Bart

[mancubus]

hi.

check this out:

http://dutchgemini.wordpress.com/2009/12/03/change-the-sheets-codename-in-excel-using-vba/

[GTO]

Greetings Bart,

I did not try yours, so not sure about the error. Try:


Option Explicit

Sub Main()
Dim wksNew As Worksheet

Const PRODHX As String = "Producthistorie"

With ThisWorkbook
'// Ensure 'Producthistorie' exists before placing/moving //
If ShExists(PRODHX) Then
Set wksNew = .Worksheets.Add(After:=.Worksheets(PRODHX))
Else
Set wksNew = .Worksheets.Add(After:=.Worksheets(.Worksheets.Count))
End If
'// Ensure we don't already have a sheet named 'MATLAB' //
If Not ShExists("MATLAB", ThisWorkbook) Then
wksNew.Name = "MATLAB"
End If
'// Ensure we don't already have a sheet w/CodeName of 'shtMATLAB'. Use the //
'// hidden property '_CodeName' //
If Not CodeNameExists("shtMATLAB", ThisWorkbook) Then
wksNew.Parent.VBProject.VBComponents(wksNew.CodeName).Properties("_CodeName") = "shtMATLAB"
End If
End With
End Sub

Function CodeNameExists(NewCodeName As String, Optional wb As Workbook) As Boolean

If wb Is Nothing Then Set wb = ThisWorkbook

On Error Resume Next
CodeNameExists = Not wb.VBProject.VBComponents(NewCodeName) Is Nothing
On Error GoTo 0
End Function

Function ShExists(ShName As String, Optional wb As Workbook) As Boolean

If wb Is Nothing Then Set wb = ThisWorkbook

On Error Resume Next
ShExists = wb.Worksheets(ShName).Name = ShName
On Error GoTo 0
End Function


Hope that helps,

Mark

[mikerickson]

Is the code in the code module of the sheet whose code name is being changed? I would avoid that.