how to use codenames with an addin?
 

I´m writing my first addin and I´m having the following problem:

I have a workbook which retrieves data from a database. With the addin I
create a new menu that has the update functions. One update function should
be renaming 6 sheets in the workbook. Of course, once renamed, the sheets
have a different name the next time I use the update, so I want to use the
codename of the sheets as the reference. But I read somewhere that I can´t
use codenames to reference to sheets in a different workbook, hence it
wouldn´t work from the addin either.

Is there a solution or workaround to this problem?
Thanks.

how to use codenames with an addin?
 

Dim wb As Workbook
Dim ws As Worksheet
Dim sCodename As String
Dim n As Long

Set wb = Application.Workbooks("myFile.xls")

sCodename = "myCodename" ' case sensitive

For Each ws In wb.Worksheets
n = n + 1
If ws.CodeName = sCodename Then
Exit For
End If
Next

If n wb.Worksheets.Count Then
MsgBox "not found"
Else
MsgBox ws.Name, , "ws no. " & n
End If

Bear in mind there are problems returning the codename of a new sheet that's
been inserted with the VBE closed and the file not saved.

Regards,
Peter T


"Ozz" wrote in message
...
I´m writing my first addin and I´m having the following problem:

I have a workbook which retrieves data from a database. With the addin I
create a new menu that has the update functions. One update function
should
be renaming 6 sheets in the workbook. Of course, once renamed, the sheets
have a different name the next time I use the update, so I want to use the
codename of the sheets as the reference. But I read somewhere that I can´t
use codenames to reference to sheets in a different workbook, hence it
wouldn´t work from the addin either.

Is there a solution or workaround to this problem?
Thanks.