From my Addin, referring to activeworkbook's sheets by their codenames

[Dianne]

I have a workbook that has a reference to an add-in. I know that the
reference works, because I can call the addin's functions and variables from
within my workbook.

However, in my addin, I refer to the workbook's worksheets using their
codenames (so that if my users change the worksheet name, it doesn't mess up
my code). When I run the code, I get an error message:

"Object doesn't support this property or method"

Here's an example --

ActiveWorkbook has a worksheet with the codename shtDiary

Addin code looks like this:

strDiaryDate = ActiveWorkbook.shtDiary.Cells(1,1).Value2

if I change it to refer to the collection using the worksheet name it works
just fine:

strDiaryDate = ActiveWorkbook.Worksheets("Diary").Cells(1, 1).Value2

Why isn't this working? Any help greatly appreciated.

Dianne

 

[Dave Peterson]

What happens when you copy that code into a module in the real workbook? It
didn't work for me.

The only way I know to use the codename from a different workbook is to cycle
through the sheets and check:

Option Explicit
Sub testme01()

Dim wks As Worksheet
Dim mySheet1 As Worksheet

Set mySheet1 = Nothing
For Each wks In ActiveWorkbook.Worksheets
If StrComp(wks.CodeName, "sheet1", vbTextCompare) = 0 Then
Set mySheet1 = wks
Exit For
End If
Next wks

If mySheet1 Is Nothing Then
MsgBox "not found"
Else
MsgBox "Found it and its name is: " & mySheet1.Name
End If

End Sub

 

[Dianne]

What happens when you copy that code into a module in the real workbook? It
didn't work for me.

The only way I know to use the codename from a different workbook is to cycle
through the sheets and check:

Thanks Dave.

The code worked fine until I moved it to the addin.

If I have to, I'll loop through the activeworkbook worksheets, but I'm
hoping not to have to do that.

 

[Rob Bovey]

Hi Dianne,

The following function shows how to retrieve a sheet tab name of a
worksheet from another workbook based on its CodeName. However, this will
not work if run under Excel 2002 with VBProject protection turned on (which
is the default setting). If you're going to have to support Excel 2002 or
higher, looping is the better option.

Function szSheetTabName(ByRef wkbProject As Workbook, _
ByRef szCodeName As String) As String
szSheetTabName = wkbProject.VBProject _
.VBComponents(szCodeName).Properties("Name")
End Function

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

 

[Dave Peterson]

Much nicer. (And I think I've actually stolen that code from either you or
Chip.)

 

[Dianne]

Rob,

Thanks for that -- I like it! Before I rewrite all my code, however, a
couple quick questions --

This workbook will be used throughout our company on remote worksites --
probably by about 25 - 40 people. By using the worksheets' codenames, I was
trying to eliminate the possibility of problems that would arise if one of
the users renamed a sheet. However...

Is there a lot of overhead involved in calling this function each time I
need to refer to a sheet (although my code doesn't do a lot of it)? Or would
I be better off telling everyone not to rename the sheets and then in my
code I could just use ActiveWorkbook.Worksheets("SheetName")?

I've never used the VBProject object or VBComponents collection before. Is
this likely to cause any problems when distributed? By that I mean, if a
user doesn't have a full installation of Excel 97, would this code still
work?

Thanks.
Dianne