The Tricky Blank CodeName Property & Excel 2002

[Bob]

I've seen newsgroup messages back to 1999 trying to deal
with the problem where the CodeName property returns a
blank string unless the VBE gets "invoked" by one means
or another.

One of the simples solutions I've seen suggested fixing
this problem by simply making a reference to the
VBProject object as shown below:

Sub GetCodeName()
' Uncomment these two lines and .CodeName works
' because the VBProject object gets referenced.
'Dim s As String
's = ActiveWorkbook.VBProject.Name
MsgBox "CodeName = " & ActiveSheet.CodeName
End Sub

However, in Excel 2002 (and higher I assume) any
reference to the VBProject object results in the
error "Programmatic access to Visual Basic Project is not
trusted". You can make this error go away by enabling
Tools > Macros > Security > Trusted Souces > Trust access
to Visual Basic Project.

Here's the problem. I must use the CodeName property in
my Add-In that needs to work in Excel 2000 and higher. I
really do not want to have to require the user to
enable "Trust access to Visual Basic Project" in order
for my Add-In to work properly on Excel 2002 and higher.

Help! Does anyone have a solution for me?

 

[Dave Peterson]

Maybe just looping through the worksheets:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim myCodeName As String

myCodeName = ""
For Each wks In ActiveWorkbook.Worksheets
If wks.Name = ActiveSheet.Name Then
myCodeName = wks.CodeName
Exit For
End If
Next wks

If myCodeName = "" Then
MsgBox "this shouldn't happen!"
Else
MsgBox myCodeName & vbNewLine & wks.Name
End If

End Sub

 

[Bob]

Dave,

Thanks for the possible fix, but that doesn't fix the
problem in an XLA Add-In.

Oddly, the CodeName property always works properly in an
XLS, but not in an XLA. I suppose for an XLA the VBE
must be brought into the picture, thus causing CodeName
to work.

Bob

 

[Dave Peterson]

Two questions:

1. Are you saying that with the code in an xla, it fails to find the code name
in the activeworkbook?

2. Or are you saying that the no matter where the code is, it fails to find the
codename of a worksheet in an xla file?

If you meant the first, I just tried it in xl2002 and it found the correct
worksheet for codename Sheet1 in a .xls file.

If you meant the latter, then maybe it's the activeworkbook.worksheets that's
causing problems. (I've never seen a .xla file the activeworkbook.)

What happened when you tried it and what version of excel are you using?

 

[Wei-Dong Xu [MSFT]]

Hi Bob,

Thank you for posting in MSDN managed newsgroup!

For the security issue of Excel 2002, I'd suggest the kb article 317405 will provide some assistance for you. This kb article introduces the security
limitation of office XP for the office VBA environment and object model. Please go to:
282830 PRB: Programmatic Access to Office XP VBA Project Is Denied
http://support.microsoft.com/?id=282830

Furthermore, you can use the Application.AutomationSecurity property to set the macro security. The kb article 317405 will help you some. Please go
to:
317405 OFFXP: How to Implement Application.AutomationSecurity
http://support.microsoft.com/?id=317405

Please feel free to let me know if you have any further questions.

Does this answer your question? Thank you for using Microsoft NewsGroup!

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Bob]

Dave,

I am testing with both Excel 2000 and Excel 2002.

I have discovered a new twist to this issue.

In all my tests I have never seen
ActiveWorkbook.ActiveSheet.CodeName return a blank result
when the code was in an XLS.

However, in an XLA the .CodeName property returns a blank
result in some situations and works properly in other
situations. The key to whether .CodeName works or not is
tied to whether the workbook and its owned worksheets
that .CodeName is being used on have ever been "opened"
in the VBE (Alt-F11).

For example, load an XLA Add-In that displays the
CodeName of the currently active worksheet. Create a new
workbook, e.g. Book2.xls, and the Add-In will display a
blank CodeName for Sheet1. Now press Alt-F11 to open the
VBE. Then close the VBE. Now the Add-In will properly
display Sheet1 as the CodeName of the sheet. Next, save
Book2.xls to disk and then exit from Excel.

Now, to prove that once a workbook has been opened in the
VBE then CodeName will forever work after that, start
Excel again. Use the Add-In on Sheet1 of the new
workbook Book1.xls that Excel creates by default, and you
will find that CodeName is blank. Now open Book2.xls
that was previously saved to disk, and the Add-In will
properly show that CodeName for Sheet1 is Sheet1. Click
back on Book1 and CodeName doesn't work on that
workbook. Click back on Book2.xls and CodeName works
properly on all sheets of that workbook.

Apparently, opening a workbook in the VBE does something
to that workbook that persists to disk and thereafter
CodeName in an Add-In works properly on that workbook.

I hope Microsoft fixes this. As for me, I've abandoned
using CodeName in my Add-In and have come up with an
alternative approach where I use the sheet name along
with a pseudo "sheet rename event" suggested by Shah
Shailesh.

Bob

-----Original Message-----
Two questions:

1. Are you saying that with the code in an xla, it

fails to find the code name in the activeworkbook?

2. Or are you saying that the no matter where the code

is, it fails to find the codename of a worksheet in an
xla file?

If you meant the first, I just tried it in xl2002 and it

found the correct worksheet for codename Sheet1 in a .xls
file.

If you meant the latter, then maybe it's the

activeworkbook.worksheets that's causing problems. (I've
never seen a .xla file the activeworkbook.)

 

[Dave Peterson]

Yep. I see it.

In fact, I think it's been a problem for awhile (since xl97 at least). I used
to have a routine that was based on David McRitchie's Table of contents
(http://www.mvps.org/dmcritchie/excel/buildtoc.htm) and I wanted to print the
codename. But the added worksheet's codename never would appear.

(I just dumped the codename--it wasn't important enough to me to keep.)

But maybe you could use something like this:

In a general module:

Option Explicit
Private Declare Function LockWindowUpdate Lib "USER32" _
(ByVal hwndLock As Long) As Long
Private Declare Function GetDesktopWindow Lib "USER32" () As Long
Sub WindowUpdating(Enabled As Boolean)

'Completely Locks the Whole Application Screen Area,
'including dialogs and the mouse.

Dim Res As Long

If Enabled Then
LockWindowUpdate 0
'Unlock screen area
Else
Res = LockWindowUpdate(GetDesktopWindow)
'Lock at desktop level
End If

End Sub

Sub testme01()

With Application.VBE.MainWindow
Call WindowUpdating(False)
.Visible = True
.Visible = False
Call WindowUpdating(True)
End With

MsgBox ActiveWorkbook.ActiveSheet.CodeName

End Sub


But SAVE your work before you run it (in every open application!).

The windowupdating stuff actually freezes the pc. If something bad happens,
you'll have to reboot.

(application.screenupdating stops excel from flickering, but doesn't have any
affect on other applications (like the VBE--kind of another app.))

(If I get industrious, I may add it to my stolen version of David McRitchie's
code.)

And it seemed to work ok for me under win98 and xl2002.

 

[Shailesh Shah]

Hi Bob,

With Dave Peterson's suggested method try this code.

Assign this macro to a custom button in your toolbar. Close your VBE
Editor. Create a New workbook & click your custom button. Works in
xl-2000.


Sub ShowCodeName()
Application.VBE.MainWindow.Visible = False
MsgBox ActiveSheet.CodeName
End Sub


Regards,
Shah Shailesh
http://members.lycos.co.uk/shahweb/


----------------------------------------------------

 

[Bob]

Shah,

Thanks for the suggestion, but accessing any property of
Application.VBE results in the error "Programmatic access
to Visual Basic Project is not trusted" in Excel 2002. I
don't want to require users of my Add-In to lower their
security simply so that my Add-In works properly.

As a result, I've abandoned using CodeName and am using
worksheet.Name along with your method of detecting when a
worksheet has been renamed.

Bob

 

[Bob]

Dave,

Thanks for the suggestion, but accessing any property of
Application.VBE results in the error "Programmatic access
to Visual Basic Project is not trusted" in Excel 2002. I
don't want to require users of my Add-In to lower their
security simply so that my Add-In works properly.

Bob